Post reply

Help requested on getdate

  • April 4, 2007 at 9:15 am

    #171962

    Hi-

      I am trying to figure out how to obtain data for a particular day without having to re-type the new day everyday.  Basically I am looking at a backorder file that appends new data daily and has a field called "Backorder_date", which would be equal to today's date if the item went into backorder.  In my query I have a select where backorder_date=getdate()  but that will not work because the getdate() will not be equal to the backorder_date because of the time. 

    Is there a way to ignore the time and just look at the actual date?

     

    Thanks for your help!

  • April 4, 2007 at 9:29 am

    #698574

    Try this:

     

    select

    convert(smalldatetime,convert(varchar(10),getdate(),101))

     

  • April 4, 2007 at 9:40 am

    #698575

    Or try this:

    select dateadd(dd,datediff(dd,getdate()),0)

  • April 4, 2007 at 10:17 am

    #698595

    I think you have a typo.  This will do what you want.

    select dateadd(dd,datediff(dd,0,getdate()),0)

  • April 4, 2007 at 10:22 am

    #698597

    That code would produce an error if the dateformat in non-US, for example dmy is normal in UK.

     

     

  • April 4, 2007 at 10:40 am

    #698598

    The first code worked perfect.  Thank you very much for your assistance!

  • April 5, 2007 at 9:59 am

    #698845

    even if it works for you, you should still use this method

    select dateadd(day, datediff(day, 0, getdate()), 0)

    as it is not depend on your date format or language settings. It will work anywhere. And it is definitely faster than the double convert method.

  • April 5, 2007 at 10:16 am

    #698852

    Thanks!  I'll use this one. 

  • April 5, 2007 at 10:58 am

    #698865

    Michael, Regarding the typo, that's what I get for writing the select statement on one system and typing it on another (didn't use cut and paste).

    Thanks

  • April 5, 2007 at 11:03 am

    #698868

    Well ... it gives error on my SQL Server

    select dateadd(dd,datediff(dd,getdate()),0)

    Server: Msg 174, Level 15, State 1, Line 1

    The datediff function requires 3 arguments.

  • April 5, 2007 at 11:29 am

    #698880

    KH, That's because of the typo.  I left out the '0,' after the 'dateadd(dd,', which is what Michael was tell me.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply