locked
Manipulating GetDate() to start at Midnight rather than now RRS feed

  • Question

  • Hi Folks,

     

    I am trying to find the best way to use the getDate() function in SQL Server CE to return a date and time which starts at midnight rather than the value of now which getDate() returns.

     

    When running getDate I get the value of 29/04/2008 10:48:33 returned, but I want to return 29/04/2008 00:00:00 instead.

    The only way I can see to do this is like so:

     

    Code Snippet

    --SQL to get shifts for next 7 days.

    select * from SHIFTS

    where STARTDATE between

    --Today at midnight 2008-04-29 00:00:00.000

    (convert(datetime,

    convert(nvarchar,(datepart(yyyy,getdate()))) + '/'

    +

    convert(nvarchar,(datepart(MM,getdate()))) + '/'

    +

    convert(nvarchar,(datepart(dd,getdate())))

    ))

    and

    --7 days from now at night 2008-05-05 00:00:00.000

    (

    convert( datetime,

    convert(nvarchar,(datepart(yyyy,getdate()+6))) + '/'

    +

    convert(nvarchar,(datepart(MM,getdate()+6))) + '/'

    +

    convert(nvarchar,(datepart(dd,getdate()+6)))

    ))

     

     

    Is there a better way to do this rather than this long winded method?

     

    Thanks,

     

    Morris

     

    Tuesday, April 29, 2008 10:12 AM

Answers

  • Try

     

    Code Snippet

    WHERE STARTDATE BETWEEN DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())) -- Midnight last night

    , DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())+6) -- Midnight in 7 days

     

     

     

    • Marked as answer by ErikEJMVP Tuesday, August 18, 2020 12:01 PM
    Tuesday, April 29, 2008 12:23 PM
  • I like to do it this way:

    select cast( floor( cast( getdate() as float)) as datetime)


    • Marked as answer by ErikEJMVP Tuesday, August 18, 2020 12:01 PM
    Thursday, August 13, 2020 8:57 PM

All replies

  • Try

     

    Code Snippet

    WHERE STARTDATE BETWEEN DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())) -- Midnight last night

    , DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())+6) -- Midnight in 7 days

     

     

     

    • Marked as answer by ErikEJMVP Tuesday, August 18, 2020 12:01 PM
    Tuesday, April 29, 2008 12:23 PM
  • I like to do it this way:

    select cast( floor( cast( getdate() as float)) as datetime)


    • Marked as answer by ErikEJMVP Tuesday, August 18, 2020 12:01 PM
    Thursday, August 13, 2020 8:57 PM