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

[rdraider]

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=toda y) 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.

[ZeldorBlat]

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


rdraider wrote:[color=blue]
> 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=toda y) 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.[/color]

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(datetim e, convert(varchar (10), aud_dt, 1) + ' ' +
convert(varchar (10), aud_tm, 14))

[rdraider]

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

These both work well. Miliseconds don't matter.

Thank you.

"ZeldorBlat " <zeldorblat@gma il.com> wrote in message
news:1149832253 .251499.139790@ h76g2000cwa.goo glegroups.com.. .[color=blue]
>
> rdraider wrote:[color=green]
>> 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=toda y) 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.[/color]
>
> 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(datetim e, convert(varchar (10), aud_dt, 1) + ' ' +
> convert(varchar (10), aud_tm, 14))
>[/color]