Post reply

SQL query -- searching for part of a datetime value.

  • September 10, 2004 at 2:32 pm

    #86441

    Hi,

    I have a SALE table where I need to search for all sales made on sept, 20 1999.  The date field is of type datetime and is called SaleDateTime.  In EM, I see the values of this field like this '20/09/1999 12:48:32 PM'.  IN QA I see it like this '1999-09-20 12:48:32.470'.  How can I search for the date part only of the datetime function..  I tried

    where saledatetime like '%1999-09-20%' or

    where saledatetime like '%20/09/1999%'

    where saledatetime like '%sept 20, 1999%'

    I never get any results.  I tried the year only and it works..

    where saledatetime like '%1999%' but as soon as I add the month and separator, it does not work.  I tried - and / as separators.

    Please help... 

  • September 10, 2004 at 2:56 pm

    #522644

    Would converting to a string answer your problem?  For example, the select statement below finds all of the sales made on one date.  The print statement are an example of how the time is stripped from the time string.

     

    use Northwind

    select *

    from Orders

    where CONVERT(varchar(50),OrderDate,105) ='11-07-1996'

     

    print CONVERT(varchar(50),CONVERT(datetime,'20/09/1999 12:48:32 PM',105),105)

    print CONVERT(varchar(50),CONVERT(datetime, '20/09/1999',105),105)



    Everett Wilson
    ewilson10@yahoo.com

  • September 10, 2004 at 3:07 pm

    #522647

    thanks.. It works like a charm.. 

Viewing 3 posts - 1 through 2 (of 2 total)

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