Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 querying date and time

Author  Topic 

sql_n00b
Starting Member

2 Posts

Posted - 2009-04-23 : 11:59:27
I am trying to grab data off of 2 tables in MS-SQL 2005 - tblRawData and tblCalculatedData - using criteria I mention below.
tblRawData contains 2 Date columns called 'StartDate', and 'StopDate' and 2 Time columns called 'StartTime' and 'StopTime'. Now, why there are separate date and time columns I do not know, but they were made before I started working on this DB.

tblCalculatedData contains a 'SampledDate' column which is a combined date+time column (no separate date and time column in this table). Samples are recorded on the hour everyday until the person in charge feels the need to stop taking any more samples.

Now here's the criteria for querying:

The date part of SampledDate should lie between the ‘StartDate’ and ‘StopDate’ (both inclusive)
Also, the time part of SampledDate should lie between the ‘StartTime’ and ‘StopTime’ (both inclusive)
Here's an example of what I am trying to achieve:

Let's say the SampledDate column begins on 01/01/2009 00:00 and goes all the way down to 04/15/2009 17:00.
Now assume the value for 'StartDate' is 01/06/2009 and 'StopDate' is 04/14/2009.

And the corresponding 'StartTime' is 11:00 and 'StopTime' is 16:00

Now I want to obtain all records that lie between 01/06/2009 11:00 and 04/14/2009 16:00 both inclusive and ignore records that occur before and after these dates+times.

I have been able to get the required dates using the ‘BETWEEN’ keyword but the time part is throwing me off. It seems to me like I am missing something very straightforward but can’t figure out what it is.
Thanks!

Qualis
Posting Yak Master

145 Posts

Posted - 2009-04-23 : 16:21:55
Try this:
(I create tmp tables and fill them w/ data to simulate your dataset. At the end is your query.)

Declare @tblRawData Table
(
ID int identity(1,1),
StartDate date,
StopDate date,
StartTime time,
StopTime time,
RawData varchar(6000)
)

Declare @tblCalculatedData Table
(
ID int identity(1,1),
SampledDate datetime,
CalculatedData varchar(100)
)

Declare @cnt int
Declare @Date datetime
Set @cnt = 1
Set @Date = '01/01/2009'

While @cnt <= 100 Begin
If @cnt % 5 = 1 Begin
Insert Into @tblRawData
Select CAST(@Date as date), DateAdd(d, 5, CAST(@Date as date)),
CAST(@Date as time), DateAdd(MINUTE, 10, CAST(@Date as time)),
REPLICATE('test', @cnt)
End

Insert Into @tblCalculatedData
Select DateAdd(hh, 5, @Date),
'test' + CAST(@cnt as varchar)

Set @Date = DateAdd(d, 1, CAST(@Date as date))
Set @cnt = @cnt + 1
End


-----------------------------------------------------
-----------------------------------------------------

Select *
From @tblRawData
Inner Join @tblCalculatedData On SampledDate Between
Cast(Left(Cast(StartDate as varchar) + ' ' + Cast(StartTime as varchar), 23) as datetime) And
Cast(Left(Cast(StopDate as varchar) + ' ' + Cast(StopTime as varchar), 23) as datetime)
Go to Top of Page

sql_n00b
Starting Member

2 Posts

Posted - 2009-04-24 : 09:50:03
Thanks Qualis for your help; that was really very useful. Cheers!
Go to Top of Page
   

- Advertisement -