Search by Auto Fill Date
-
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
-
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
-
Yes.....It is a datetime format. Is this possible if end user can search by only date?
Thanks
-
Try using datapart with day, month, year.
-
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
-
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