473,396 Members | 2,070 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

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 1601

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: David Heller | last post by:
I have this schema I exported from a database running mysql version 4.01 I'm trying to create this table on a server running mysql 3.23 I keep getting a syntax error near line 11 (I added the line...
7
by: Eddy | last post by:
I have to check whether a given date is between a day and a month. A guided tour is only scheduled from november 1st until april 1st. when i want to make a reservation for the tour Today the query...
3
by: ricolee99 | last post by:
Hi everyone, I have a problem that i have been trying to solve for awhile. I'm given a code where the purpose is to create a general dataset mapper. Given any dataset, i have a class,...
2
by: stargate03 | last post by:
Hi there I have a MYSQL database which has the following table CREATE TABLE `#__content` ( `id` int(11) unsigned NOT NULL auto_increment, `title` varchar(100) NOT NULL default '', ...
5
by: Sam | last post by:
Hi, I have one table like : MyTable {field1, field2, startdate, enddate} I want to have the count of field1 between startdate and enddate, and the count of field2 where field2 = 1 between...
3
by: Dinesh | last post by:
Hi Experts, I am working on SSRS 2005, and I am facing a problem in counting the no of days. My database has many fields but here I am using only two fields They are Placement_Date and...
7
by: billygotee | last post by:
Hi, Okay this is taking longer to figure out than I thought it would. The integer members of a DateTime (such as DateTime.Minutes, DateTime.Seconds, etc.) can only get the value, not set it. ...
9
by: sheenaa | last post by:
Hello frdz, I m working with SQL SERVER 2005. My problem is with the creation of foreign key for some table thru which i m not able to insert the data. This table are samples i have included...
1
by: Joey Fontaine | last post by:
I just noticed that, when using intellisense, the DateTime.MaxValue field has a static property icon whereas the Decimal.MaxValue field has a constant property icon. However, when looking at the...
4
by: psycho007 | last post by:
Hi I have a caller ID software that I would like to change from Microsoft Access database to Mysql and was wondering if somebody could help me. The code i currently have is: Private Sub...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.