locked
Getting previous year data using T-sql RRS feed

  • Question

  • Hi,

    I am having a variable which I declared as below (06/21/2013 --> mm/dd/yyyy) format


    declare @myVar varchar(10)
    set @myVar = '06/21/2013'

    How can I get the output as below:-

    "06/01/2012"  

     and

    "06/20/2012"

    Basically I want to get the data for the previous year. Month will remain same as my input and date range should be 1 to 20

    Please advise.

    Thanks, 

    Monday, July 8, 2013 6:58 AM

Answers

  • Thanks,

    I have resolve like below format:

    select CONVERT(VARCHAR(11),DATEADD(mm, DATEDIFF(mm, 0, dateadd(yy,-1,@myVar)), 0),101) 
       ,CONVERT(VARCHAR(11),dateadd(day, -1, dateadd(yy,-1,@myVar)),101)

    • Marked as answer by UltraDev Monday, July 8, 2013 7:08 AM
    Monday, July 8, 2013 7:07 AM
  • Try also

    declare @myVar varchar(10)
    set @myVar = '06/21/2013' 
    
    select convert(datetime,substring(convert(varchar,dateadd(dd,-1,dateadd(yy,-1,cast(@myVar as datetime))),112),1,6)+'01',112),
    dateadd(dd,-1,dateadd(yy,-1,cast(@myVar as datetime)))
    go
    


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by UltraDev Monday, July 8, 2013 10:59 AM
    Monday, July 8, 2013 7:08 AM

All replies

  • Try

    select convert(datetime,substring(convert(varchar,dateadd(dd,-1,dateadd(yy,-1,cast('06/21/2013' as datetime))),112),1,6)+'01',112)
    go
    
    select dateadd(dd,-1,dateadd(yy,-1,cast('06/21/2013' as datetime)))
    go
    


    Many Thanks & Best Regards, Hua Min

    Monday, July 8, 2013 7:06 AM
  • Thanks,

    I have resolve like below format:

    select CONVERT(VARCHAR(11),DATEADD(mm, DATEDIFF(mm, 0, dateadd(yy,-1,@myVar)), 0),101) 
       ,CONVERT(VARCHAR(11),dateadd(day, -1, dateadd(yy,-1,@myVar)),101)

    • Marked as answer by UltraDev Monday, July 8, 2013 7:08 AM
    Monday, July 8, 2013 7:07 AM
  • Convert from string date to DATE data type. Use the DATEADD function to subtract a year.

    Datetime conversion:

    http://www.sqlusa.com/bestpractices/datetimeconversion/

    It is best to store date as DATE/DATETIME/DATETIME2 data type.  String dates can be all different formats.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Monday, July 8, 2013 7:08 AM
  • Try also

    declare @myVar varchar(10)
    set @myVar = '06/21/2013' 
    
    select convert(datetime,substring(convert(varchar,dateadd(dd,-1,dateadd(yy,-1,cast(@myVar as datetime))),112),1,6)+'01',112),
    dateadd(dd,-1,dateadd(yy,-1,cast(@myVar as datetime)))
    go
    


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by UltraDev Monday, July 8, 2013 10:59 AM
    Monday, July 8, 2013 7:08 AM
  • Thanks Hua Min, That is also correct.
    Monday, July 8, 2013 11:00 AM