locked
How to get date and year range in SQL? RRS feed

  • Question

  • User221424154 posted

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

    Thursday, September 5, 2019 7:00 AM

Answers

  • User-719153870 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 5, 2019 8:17 AM
  • User475983607 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 5, 2019 6:50 PM

All replies

  • User-719153870 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 5, 2019 8:17 AM
  • User753101303 posted

    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).

    Thursday, September 5, 2019 8:32 AM
  • User77042963 posted

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

    Thursday, September 5, 2019 2:45 PM
  • User221424154 posted
    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]
    Thursday, September 5, 2019 6:41 PM
  • User475983607 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 5, 2019 6:50 PM
  • User-719153870 posted

    Hi 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

    Friday, September 6, 2019 1:09 AM
  • User77042963 posted


    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()) )

    Friday, September 6, 2019 2:32 PM