Advice on how to combine date and time into one field

KevCB226 · #1 (**permalink**) 01-27-12, 11:03

I'm looking for help to combine a date and time field to make one field.

We have a piece of software and database for student registers.

One of the biggest problems is the database has been difficult to work with, as I have had to work out the date using dateadd functions and combine fields from different tables.
I've now got the date correctly, but I want to add the time to the time part of a datetime field.

The time field is already stored in a datetime field, but the date in this field is always 1899-12-30.

This is the function I am using to get the date:

Code:

DATEADD(dd, e.day_of_week, DATEADD(ww, sm.week, '01/08/2011'))

And this is the function I use to get the time (from a datetime field):

Code:

SUBSTRING(CAST(e.start_time AS varchar(20)), 13, 5)

With the DATEADD function it appears you can only add individual elements each time hh, mi, ss.

Would I have to add a DATEADD function for each time element in order to add the time to the date?

MCrowley · #2 (**permalink**) 01-27-12, 11:26

There are a couple ways to do this, depending on the precision you need, and the version you have. If you have SQL 2008 or better, you can just use the TIME datatype to take the time out. Then you can add that to to the datetime representation of the date.

Code:

declare @timeportion datetime

set @timeportion = '1899/12/31 12:38'

select convert(time, @timeportion), convert(datetime, '1/1/2012') + convert(time, @timeportion)

If you are on SQL 2005 or lower, then you will need to derive how many seconds past midnight the time is, and add that to your date.

Code:

declare @timeportion datetime

set @timeportion = '1899/12/31 12:38'

select datediff (ss, '1899/12/31', @timeportion), dateadd (ss, datediff (ss, '1899/12/31', @timeportion), '1/1/2012')

Naturally, you can use milliseconds, if you need that level of precision. Most reports don't need that level of precision, though. Most get away with minutes.

Pat Phelan · #3 (**permalink**) 01-27-12, 12:49

When dealing with hinky data, I always take the ultra-conservative approach to the fix. I would use:

Code:

DateAdd(day, DateDiff(day, e.start_time, '2011-01-08t00:00') 
+  7 * sm.[week] + e.day_of_week, e.start_time)

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech