How to get date and year range in SQL?[Answered] RSS

7 replies

Last post Sep 06, 2019 02:32 PM by limno

‹ Previous Thread|Next Thread ›

Shortcuts

- Reply
Manishamani
Member

13 Points

25 Posts

How to get date and year range in SQL?

Sep 05, 2019 07:01 AM|Manishamani|LINK

I need to get the date and year format like 2018-2019/09-005 and if year change it will automatically change
- Reply
Yang Shen
Contributor

3140 Points

983 Posts
Re: How to get date and year range in SQL?

Sep 05, 2019 08:17 AM|Yang Shen|LINK
Hi Manishamani,

Manishamani
2018-2019/09-005

What is that 005 in your sample?

Is it like [min year in your table]-[max year in your table]/[this month]-[today's day]?

The 2018 is the earliest year in your table or a static value you set?

In this case, you will need to know several methods in SQL:
```
convert(type,value)-- convert a value to a type type value

getdate()-- get today's date

year(date),month(date),day(date)-- get the year,month,day of a date date

right(text,n)-- select n chars of text from right to left
```
Please refer tp below code:
```
select (CONVERT(varchar(50),(select MIN(YEAR(UDate)) from Users)))+'-'+(CONVERT(varchar(50),YEAR(getdate())))+'/'+right('00'+(CONVERT(varchar(50),month(getdate()))),2)+'-'+right('00'+(CONVERT(varchar(50),day(getdate()))),3) as d
```
This might get what you want:

Best Regard,

Yang Shen
- Reply
PatriceSc
All-Star

48500 Points

18071 Posts

Re: How to get date and year range in SQL?

Sep 05, 2019 08:32 AM|PatriceSc|LINK

Hi,

Depending on your db you have functions such as GETDATE() or GETUTCDATE() to get the current date (SQL Server). The YEAR() function allows to extract the year. Check the doc for the DBMS you are using.

If you want to select data within two dates my personal preference is to use MyCol>=@StartDate AND MyCol<@EndDate (@EndDate being for example the 1/1 for the next year so that data for 31/12 are taken until right before midnight).
- Reply
limno
All-Star

123252 Points

10024 Posts

Moderator

Re: How to get date and year range in SQL?

Sep 05, 2019 02:45 PM|limno|LINK

What are you try to do? You can format your datetime data in the form you choose.

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
- Reply
Manishamani
Member

13 Points

25 Posts

Re: How to get date and year range in SQL?

Sep 05, 2019 06:41 PM|Manishamani|LINK
Hi

2018 is not a static value . It should be previous year of current year .
O/p should be like
[Previous year]-[current year]/[0+ current month]/-[00+ current date]
- Reply
mgebhard
All-Star

53001 Points

23587 Posts
Re: How to get date and year range in SQL?

Sep 05, 2019 06:50 PM|mgebhard|LINK
Manishamani
Hi

2018 is not a static value . It should be previous year of current year .
O/p should be like
[Previous year]-[current year]/[0+ current month]/-[00+ current date]

Use SQL DateTime and string functions. See the standard T-SQL documentation.
```
DECLARE @now DATETIME = GETDATE()

SELECT CAST(Year(@now)-1 AS VARCHAR(4)) 
		+ '-' + 
		CAST(Year(@now) AS VARCHAR(4)) 
		+ '/0' +
		CAST(MONTH(@now) AS VARCHAR(2))
		+ '-00' +
		CAST(DAY(@now) AS VARCHAR(2))
```
https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-functions-transact-sql?view=sql-server-2017
- Reply
Yang Shen
Contributor

3140 Points

983 Posts
Re: How to get date and year range in SQL?

Sep 06, 2019 01:09 AM|Yang Shen|LINK
Hi Manishamani,

Manishamani
2018 is not a static value . It should be previous year of current year .

That's even easier then:
```
select (CONVERT(varchar(50),(year(GETDATE())-1)))+'-'+(CONVERT(varchar(50),YEAR(getdate())))+'/'+right('00'+(CONVERT(varchar(50),month(getdate()))),2)+'-'+right('00'+(CONVERT(varchar(50),day(getdate()))),3) as d
```
Hope this can help.

Best Regard,

Yang Shen
- Reply
limno
All-Star

123252 Points

10024 Posts

Moderator
Re: How to get date and year range in SQL?

Sep 06, 2019 02:32 PM|limno|LINK
Manishamani

I need to get the date and year format like 2018-2019/09-005 and if year change it will automatically change
```
select 
Concat(Format(dateadd(year,-1,getdate()),'yyyy')
,'-',Format(getdate(),'yyyy'),'/0'
,Month(getdate()),'-00',day(getdate()) )

--or
select 
Concat(Year(getdate())-1
,'-',Year(getdate()),'/0'
,Month(getdate()),'-00',day(getdate()) )
```
Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm