Posted by ZeldorBlat on 06/09/06 05:50

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))

[Back to original message]