Post reply

Search by Auto Fill Date

  • August 25, 2004 at 11:50 am

    #112195

    Table has ten fields. One of the filed is date_received. When end user adds new records this date field automattically (2004-08-20 00:00:00.000

    ) get updated by system date and rest of the fields key in by user.

    When I am running query, I am not getting any data.

    Query: Select * from table where date_received like '2004-08-20%'

    Thanks

  • August 25, 2004 at 2:24 pm

    #520614

    I'm assuming that the date_received column is datetime format.  ...like '2004-08-20%' is character string manipulation and wouldn't work for datetime format.

    What you need is something like...

    select * from table

    where date_received between '2004-08-20 00:00:00' and '2004-08-20 23:59:59'

    Steve

  • August 25, 2004 at 2:36 pm

    #520615

    Yes.....It is a datetime format. Is this possible if end user can search by only date?

    Thanks

  • August 26, 2004 at 3:53 am

    #520668

    Try using datapart with day, month, year.

  • August 26, 2004 at 4:29 am

    #520669

    Another method to effectively cover all times within a given day is

    select * from table

    where date_received >= '2004-08-20' and date_received < '2004-08-21'

    (assuming that you want all records from the day of '2004-08-20' regardless of the timepart.)

    /Kenneth

  • August 26, 2004 at 6:42 am

    #520685

    You can also use the CONVERT function - see sql online books for detail  or help.

    Using CONVERT you can cater for different date format eg US, British, etc and/or convert the date to varchar. 

    In your example, you quoted format yyyy-mm-dd.  This style equivalent to 120 in CONVERT, so your query would look something like:

    select  * from tbl_table

    where convert(varchar(10),date_received,120)='2004-08-20'

    Cheers,

    Serdal

Viewing 6 posts - 1 through 5 (of 5 total)

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