extracting the time from a date time field

[theboyholty]

I've got two fields - DateCreated (e.g. 2008-12-19 00:00:00.000) and TimeCreated (e.g. 1900-01-01 16:07:35.000).
Where the 1900-01-01 is a set dummy date - its just the time that's accurate.
I want to show items recieved in the last 10 minutes but if I use datediff(minute,timecreated,getdate()), I get as a result the number of minutes since 1900 i.e. about 57 million.
How can I strip the time out so I can compare it with the time now and show the correct records?

 

[PHV]

datediff(minute,timecreated,getdate())
this is NOT ANSI SQL syntax ...

In your SQL dialect you may perhaps simply use this:
datediff(minute,DateCreated+timecreated,getdate())

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

I've got two fields - DateCreated (e.g. 2008-12-19 00:00:00.000) and TimeCreated (e.g. 1900-01-01 16:07:35.000).

why are these two separate columns?


all your problems would go away if you had just one column


r937.com | rudy.ca

 

[PHV]

I'm with Rudy: why storing a single value (DateTimeCreated) in two columns ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[theboyholty]

That's a great question. Unfortunately I didn't design the database, i'm just querying it. One can only work with the tools one is given. Anyway i'll try PHV's suggestin on Monday. Thanks all.

 

[theboyholty]

PHV, you are very much The Man.

Cheers bud, it works a treat - I wouldn't have thought of that.