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

7 Points

19 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

2060 Points

655 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

46040 Points

16771 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

123018 Points

9955 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

7 Points

19 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

44341 Points

19129 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

2060 Points

655 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

123018 Points

9955 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