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
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).
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]
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))
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
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
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,
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:
Please refer tp below code:
This might get what you want:
Best Regard,
Yang Shen
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).
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.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Member
13 Points
25 Posts
Re: How to get date and year range in SQL?
Sep 05, 2019 06:41 PM|Manishamani|LINK
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]
All-Star
53001 Points
23587 Posts
Re: How to get date and year range in SQL?
Sep 05, 2019 06:50 PM|mgebhard|LINK
Use SQL DateTime and string functions. See the standard T-SQL documentation.
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
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,
That's even easier then:
Hope this can help.
Best Regard,
Yang Shen
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
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm