Posted by rdraider on 06/09/06 16:56

These both work well. Miliseconds don't matter.

Thank you.

"ZeldorBlat" <zeldorblat@gmail.com> wrote in message
news:1149832253.251499.139790@h76g2000cwa.googlegroups.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))
>

[Back to original message]