469,291 Members | 1,717 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,291 developers. It's quick & easy.

Help with 2 datetime fields-1 stores date, the other time

Hi,
We have a lame app that uses 2 datetime(8) fields, 1 stores the date, the
other the time.
example query:

select aud_dt, aud_tm
from orders

results:
aud_dt aud_tm
2006-06-08 00:00:00.000 1900-01-01 12:32:26.287

I'm trying to create a query that give me records from the current date in
the past hour.
Here's a script that gives me todays date but I cannot figure out the time:

select aud_dt, aud_tm, datediff(d,aud_dt,getdate()), datediff(mi, aud_tm,
getdate())
from orders
where (datediff(d,aud_dt,getdate()) = 0)

results:
aud_dt aud_tm
datediff(0=today) timediff (since 1900-01-01)
2006-06-08 00:00:00.000 1900-01-01 12:32:26.287 0
55978689
I added this next part to the above query but it does not work since the
date/time is from 1900-01-01
and (datediff(mi, aud_tm, getdate()) <= 60)
Thanks for any help.

Jun 8 '06 #1
2 1499

rdraider wrote:
Hi,
We have a lame app that uses 2 datetime(8) fields, 1 stores the date, the
other the time.
example query:

select aud_dt, aud_tm
from orders

results:
aud_dt aud_tm
2006-06-08 00:00:00.000 1900-01-01 12:32:26.287

I'm trying to create a query that give me records from the current date in
the past hour.
Here's a script that gives me todays date but I cannot figure out the time:

select aud_dt, aud_tm, datediff(d,aud_dt,getdate()), datediff(mi, aud_tm,
getdate())
from orders
where (datediff(d,aud_dt,getdate()) = 0)

results:
aud_dt aud_tm
datediff(0=today) timediff (since 1900-01-01)
2006-06-08 00:00:00.000 1900-01-01 12:32:26.287 0
55978689
I added this next part to the above query but it does not work since the
date/time is from 1900-01-01
and (datediff(mi, aud_tm, getdate()) <= 60)
Thanks for any help.


The correct way would be to fix the database and use one datetime
column. I'll assume you already know this and that it isn't possible
for some reason.

So, if you want to combine those two into one datetime field (which you
could then use in a query however you like) you can use something like
this:

cast((cast(aud_dt as float) + cast(aud_tm as float)) as datetime)

although you might lose some precision in the miliseconds. If that's
unacceptable, you can instead do this:

convert(datetime, convert(varchar(10), aud_dt, 1) + ' ' +
convert(varchar(10), aud_tm, 14))

Jun 9 '06 #2
These both work well. Miliseconds don't matter.

Thank you.

"ZeldorBlat" <ze********@gmail.com> wrote in message
news:11**********************@h76g2000cwa.googlegr oups.com...

rdraider wrote:
Hi,
We have a lame app that uses 2 datetime(8) fields, 1 stores the date, the
other the time.
example query:

select aud_dt, aud_tm
from orders

results:
aud_dt aud_tm
2006-06-08 00:00:00.000 1900-01-01 12:32:26.287

I'm trying to create a query that give me records from the current date
in
the past hour.
Here's a script that gives me todays date but I cannot figure out the
time:

select aud_dt, aud_tm, datediff(d,aud_dt,getdate()), datediff(mi, aud_tm,
getdate())
from orders
where (datediff(d,aud_dt,getdate()) = 0)

results:
aud_dt aud_tm
datediff(0=today) timediff (since 1900-01-01)
2006-06-08 00:00:00.000 1900-01-01 12:32:26.287 0
55978689
I added this next part to the above query but it does not work since the
date/time is from 1900-01-01
and (datediff(mi, aud_tm, getdate()) <= 60)
Thanks for any help.


The correct way would be to fix the database and use one datetime
column. I'll assume you already know this and that it isn't possible
for some reason.

So, if you want to combine those two into one datetime field (which you
could then use in a query however you like) you can use something like
this:

cast((cast(aud_dt as float) + cast(aud_tm as float)) as datetime)

although you might lose some precision in the miliseconds. If that's
unacceptable, you can instead do this:

convert(datetime, convert(varchar(10), aud_dt, 1) + ' ' +
convert(varchar(10), aud_tm, 14))

Jun 9 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by David Heller | last post: by
7 posts views Thread by Eddy | last post: by
3 posts views Thread by ricolee99 | last post: by
2 posts views Thread by stargate03 | last post: by
5 posts views Thread by Sam | last post: by
7 posts views Thread by billygotee | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.