SQL Server Performance

How to get last 5 days back date

Discussion in 'SQL Server 2005 General Developer Questions' started by mpolaiah, Jul 21, 2009.

  1. mpolaiah New Member

    Hi all
    How to get last 5 days back date
    mpolaiah, Jul 21, 2009
    #2

  2. Madhivanan Moderator

    [quote user="mpolaiah"]
    Hi all
    How to get last 5 days back date
    [/quote]
    select
    dateadd(day,datediff(day,0,getdate()),-number) from
    (
    select top 5 row_number() over (order by name) as number from sysobjects
    ) as t
    Madhivanan, Jul 21, 2009
    #3

  3. Adriaan New Member

    Personally, I can't stand those silly queries against system tables to generate lists, it's just asking for trouble.
    Unfortunately, that leaves me with a clumsy query:SELECT
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
    UNION SELECT DATEADD(DAY, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
    UNION SELECT DATEADD(DAY, -2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
    UNION SELECT DATEADD(DAY, -3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
    UNION SELECT DATEADD(DAY, -4, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
    ORDER BY 1
    However, it is not terribly clear if the original poster wanted a list of dates, or simply "the date for 5 days ago" -
    SELECT DATEADD(DAY, -5, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
    or:
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) - 5

    or if he doesn't care about the time of day even:
    SELECT GETDATE() - 5
    Adriaan, Jul 21, 2009
    #4

  4. Madhivanan Moderator

    Ok. I would use thenselect
    dateadd(day,datediff(day,0,getdate()),-number) from
    (
    select 1 as number union all
    select 2 union all
    select 3 union all
    select 4 union all
    select 5
    ) as t
    Madhivanan, Jul 21, 2009
    #5

  5. FrankKalis Moderator

    [quote user="Madhivanan"]
    Ok. I would use then
    select dateadd(day,datediff(day,0,getdate()),-number) from
    (
    select 1 as number union all
    select 2 union all
    select 3 union all
    select 4 union all
    select 5
    ) as t
    [/quote]
    ...and this leaves us again with the suggestion to have a date and/or number helper table.
    FrankKalis, Jul 25, 2009
    #6

  6. Madhivanan Moderator

    [quote user="FrankKalis"]
    [quote user="Madhivanan"]
    Ok. I would use then
    select dateadd(day,datediff(day,0,getdate()),-number) from
    (
    select 1 as number union all
    select 2 union all
    select 3 union all
    select 4 union all
    select 5
    ) as t
    [/quote]
    ...and this leaves us again with the suggestion to have a date and/or number helper table.
    [/quote]
    Yes. It is
    Madhivanan, Jul 27, 2009
    #7

  7. sakthi.tnj New Member

    Hipls try the below query, I hope it will be useful for you....If you need to display the last 5 days in a single row use this query in SQL server.

    Select Convert(char,dateadd(dd,-1,getdate()),103)+' '+convert(char,dateadd(dd,-2,getdate()),103)+' '+Convert(char,dateadd(dd,-3,getdate()),103)+' '+convert(char,dateadd(dd,-4,getdate()),103)+''+Convert(char,dateadd(dd,-5,getdate()),103)

    If you need to display the last 5 days in a single column use this query in SQL server.

    Select Convert(char,dateadd(dd,-1,getdate()),103)unionSelect convert(char,dateadd(dd,-2,getdate()),103)unionselect Convert(char,dateadd(dd,-3,getdate()),103)unionSelect convert(char,dateadd(dd,-4,getdate()),103)unionselect Convert(char,dateadd(dd,-5,getdate()),103)If you have date field you can use the date field instead of getdate()
    Thanks & Regards
    Sakthimeenakshi.S
    sakthi.tnj, Jul 28, 2009
    #8

  8. Madhivanan Moderator

    [quote user="sakthi.tnj"]Hi pls try the below query, I hope it will be useful for you.... If you need to display the last 5 days in a single row use this query in SQL server.

    Select Convert(char,dateadd(dd,-1,getdate()),103)+' '+convert(char,dateadd(dd,-2,getdate()),103)+' '+ Convert(char,dateadd(dd,-3,getdate()),103)+' '+convert(char,dateadd(dd,-4,getdate()),103)+''+ Convert(char,dateadd(dd,-5,getdate()),103)

    If you need to display the last 5 days in a single column use this query in SQL server.

    Select Convert(char,dateadd(dd,-1,getdate()),103)union Select convert(char,dateadd(dd,-2,getdate()),103)union select Convert(char,dateadd(dd,-3,getdate()),103)union Select convert(char,dateadd(dd,-4,getdate()),103)union select Convert(char,dateadd(dd,-5,getdate()),103) If you have date field you can use the date field instead of getdate()
    Thanks & Regards
    Sakthimeenakshi.S[/quote]
    Well. There is no need to convert the dates to characters [:)]
    Madhivanan, Jul 29, 2009
    #9

Share This Page