I have the following values in my table
Select * From TimeSheetLogs where InTimeStamp <= '1/22/2013'
when i execute the above query i get null value
but as you can see i have 3 data with datefield as 1/22/2013
Then what i am doing wrong?
Thanks
Add a comment
|
4 Answers
that is correct because
'1/22/2013 19:21' > '1/22/2013 00:00'
2 Comments
user1960365
then what should i do to get the values of date 22 as well.
sgeddes
Select * From TimeSheetLogs where CAST(InTimeStamp as Date) <= '1/22/2013'
You probably need to truncate your dates first to remove the time portion. Then convert both sides to date datatype. The string '1/22/2013' is a string, not a date. By looking at data your InTimeStamp is timestamp datatype. You cannot compare date or timestamp to character w/out converting the char-s to date. I'm not sure what Database are you using. This is how you'd convert in Oracle using to_date function .
SELECT in_date, compare_date
FROM
(-- This is your InTimeStamp on the fly
SELECT trunc(to_timestamp('2013-01-22 16:21:19.273', 'yyyy-mm-dd hh24:mi:ss.ff')) in_date -- this is your InTimeStamp
, to_date('1/22/2013', 'mm/dd/yyyy') compare_date
FROM dual
)
WHERE in_date <= compare_date
/
Now you can compare two dates below - this is the output of above query:
in_date compare_date
---------- ------------
1/22/2013 1/22/2013
Comments
Select * From TimeSheetLogs
where InTimeStamp <= select convert(datetime,'1/22/2013',101)
Comments
As per my comment to @Zdravko's good answer, you just need to cast your date:
Select * From TimeSheetLogs where CAST(InTimeStamp as Date) <= '1/22/2013'
