September 10, 2004 at 3:22 pm
Hi All
I have a table with a smalldate data type,
In it i have the date and time eg. 10/09/2004 22:06:00
if i use QA and just look at the field DateLogged and enter in the Criteria 10/09/2004 then run, i get no records, the code is
SELECT DateLogged
FROM Call_Log
WHERE (DateLogged = CONVERT(DATETIME, '2004-09-10 00:00:00', 102))
if i remove the time from the records so i just have 10/09/2004, then the records show ok,
Does this mean i have to remove the time from all the records, i want to use smalldate or datetime as i need to run reports base on the dates, and also between dates.
I am using Delphi as the front end and when i add the data i use GetDate(),
any ideas please
Thanks in advance.
September 10, 2004 at 5:51 pm
My understanding is that datetime will hang on to the time element, meaning you either need to set all datetimes to one time (midnight) or perform the comparison in a varchar using CONVERT.
Everett Wilson
ewilson10@yahoo.com
September 11, 2004 at 1:05 am
Thanks i will give it a try.
September 11, 2004 at 4:34 am
I need to use the Date and Time as i am making a call logging system, i could i supose use a column for date and another for time but it would be easy to have it in one column, and it will be easy for the reports to be based on Datefileds,
you said about "comparison in a varchar using CONVERT" could you explain a bit more or do you have a example.
Thanks
September 11, 2004 at 10:41 am
The problem is there's no easy way to compare only dates in a datetime field. I know of a couple of ways to do it that require one extra step.
Set @val = 12/08/2004 14:53.34
The CONVERT is an easy way to strip out time when doing a day comparison. The CONVERT function allows the user to select the format the datetime value should be converted to. In the example I use 101 which is mm/dd/yyyy, 103 is dd/mm/yyyy. A full list can be found in BOL under CAST and CONVERT.
CONVERT(varchar(50),@Val,101) = '12/08/2004'
Another way is to use an upper and lower value. I believe this works becasue T-SQL implicity converts the two comparison dates to midnight of each day. Note the option of converting to one second before midnight, this can be extended to include second fractions.
CASE WHEN @val >= '12/08/2004' AND @val < '12/09/2004' (or <= '12/08/2004 23:59:59')
Everett Wilson
ewilson10@yahoo.com
September 13, 2004 at 8:48 am
By the way, the style part of the CONVERT is only used when converting from datetime TO a string. When converting from a string to a datetime don't use the style.
CONVERT(DATETIME, '2004-09-10 00:00:00')
CONVERT(VARCHAR(20), DATELOGGED, 102)
-SQLBill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply