none
How to get previous month data? RRS feed

  • Question

  • Hi,

    In my SP, I am having a variable which is @As_Date varchar(10) and it is holding date value (MM/DD/YYYY format)

    Now I want to ahieve the following scenario

    For example, 

    Initially let s say we have,

    @As_Date = '06/21/2013'

    Can you help me getting value '05/20/2013' to be precise.

    i.e from the variable value, the final value should be one day less (day=20 instead of 21 AND month=05 instead of 06)

    Hope is is clear.

    Thanks....

     

    Friday, July 5, 2013 8:52 AM

Answers

  • Hi,

    I was able to get it using below:

    declare @As_Date varchar(10)
    set @As_Date  = '06/21/2013'
    select convert(varchar(11),DATEADD(DAY, -1, DATEADD(mm,-1,@As_Date )),101)

    Thanks

    • Marked as answer by UltraDev Friday, July 5, 2013 9:08 AM
    Friday, July 5, 2013 9:08 AM

All replies

  • Try this :

    declare @As_Date varchar(10) = '06/21/2013'
    
    select DATEADD(dd,-1,DATEADD(MM,-1,CAST(@As_Date AS DATE)))

    Another :

    select CONVERT(varchar,DATEADD(dd,-1,DATEADD(MM,-1,CAST(@As_Date AS DATE))),101)


    Please vote if you find this posting was helpful or Mark it as answered.


    Friday, July 5, 2013 9:03 AM
  • Hi,

    I was able to get it using below:

    declare @As_Date varchar(10)
    set @As_Date  = '06/21/2013'
    select convert(varchar(11),DATEADD(DAY, -1, DATEADD(mm,-1,@As_Date )),101)

    Thanks

    • Marked as answer by UltraDev Friday, July 5, 2013 9:08 AM
    Friday, July 5, 2013 9:08 AM
  • You have no idea how SQL works. We have a DATE data type; strings do not hold dates!! The display format is not the internal storage format.  You also do not know that the only display format allowed in ANSI/ISO Standard SQL is the ISO-8601 Standard 'yyyy-mm-dd' as well as many, many other ISO Standards. 

    This is a fundamental concept in any tiered architecture. Plase read a book on SQL. You will probably make the mistake of using the old 1`970's Sybase/UNIX CONVERT()  string function instead of doing temporal math or creating a look-up table.  



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, July 5, 2013 6:35 PM