Join/Union or Other

I have a table that has a Time & Date column. I have them being combined on the select statement and I need to subtract 8 hours from the start time. This is an example of the current Select Statement

SELECT dateadd(hh, -8, air_date + air_time) AS theTime, station_num, database_key

This gives me the correct information but since I subtracted 8 hours I get data from the day before.

Example
SELECT dateadd(hh, -8, air_date + air_time) AS theTime, station_num, database_key
FROM table
WHERE theTime >= '2004-04-11' AND theTime <= '2004-04-12'

Airtime Airdate Output Info
8:00 AM 3/31/2004 12:00 AM 3/30/2004
4:00 PM 3/31/2004 8:00 AM 3/31/2004


Etc Etc

So on the first one since it starts at 8:00am It goes to the day before from subtracting the time.

I also have it changing the time on the insert form the flatfile. If this is a better way how should I deal with if its DST or not.


I hope all this makes sense and any help would be appreciated.

Mark

what are the datatypes of the Time & Date columns, please?

do you have a reason for carrying them as separate fields?

Here is the structure

Airdate datetime
Airtime Varchar(50)

They are in separate columns because that’s the way I get the data. I can combine them on the insert if needed. Right now I started subtracting the time in the DTS package I wrote. The problem is my data goes out 6 weeks so when it over laps daylight saving time I becomes an hour off.


Thanks
Mark

"because that’s the way I get the data"

been there, got the t-shirt

"This gives me the correct information but since I subtracted 8 hours I get data from the day before."

okay, if dateadd(hh, -8, air_date + air_time) is correct (i.e. no syntax error or anything), then i don't understand the problem

I’m sorry I guess I didn’t make the problem clear.

If a user selects that they only want to see content on a certain day lets say 4/1/04 and they submit the form. Right now its set in SQL like so.

Select dateadd(hh, -8 ,col1 + col2) AS air_date
From table
Where air_date = ‘selected_day’

So here I have taken off 8 hours for the start date and start time. Since they selected 4/1/04 in the form anything that starts before 8am is really yesterdays program not today’s cause I have taken off 8 hours.

I need it to subtract the time first then allow me to choose the date selected by the user.

Thanks
Mark