Adding a time column to a date column

[Lauren Quantrell]

I have two columns in a table:
StartDate DateTime and StartTime DateTime.
The StartDate column holds a value such as 07/16/2004
The StartTime column holds a value such as 3:00:00 PM

I want to be able to add them in a stored procedure.
When I use StartDate + StartTime I get a date two days earlier than expected.
For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns
7/14/2004 3:00:00 PM.

Can anyone point out wht I'm doing wrong with this one?

Thanks,
lq

[Steve Kass]

Lauren,

It sounds like you are using Enterprise Manager. All datetime columns in SQL Server hold both a date and a time, and if you view the
data in Query Analyzer, you should find that your StartDate column holds something like 2004-07-16 12:00:00AM and your StartTime column
holds a value like 1899-12-30 03:00:00PM.

Tools that allows data input will attach a date when a time only is entered into a SQL Server database column, and unfortunately some
tools will attach 1900-01-01 and others will attach 1899-12-30. SQL Server stores the first of these as its zero date, but it stores the
second as -2 (plus whatever fraction of a date the time portion represents, in each case). Enterprise Manager thinks that 1899-12-30 is the
base date, and both attaches it when a bare time is entered and suppresses it when it appears in a datetime to be displayed.

You are doing nothing wrong, but to be safe, you can calculate the time portion explicitly before you add. One way to do this is

StartDate + (StartTime - datediff(day,0, StartTime))

Storing time-only values in SQL Server is tricky, since there is no appropriate type. You need to be careful, and you might want to
consider alternatives, such as storing only the StartDateTime in the database, in which case you can make StartDate and StartTime computed
columns, or calculate them on the fly when you need them.

Steve Kass
Drew University

Lauren Quantrell wrote:

I have two columns in a table:
StartDate DateTime and StartTime DateTime.
The StartDate column holds a value such as 07/16/2004
The StartTime column holds a value such as 3:00:00 PM

I want to be able to add them in a stored procedure.
When I use StartDate + StartTime I get a date two days earlier than expected.
For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns
7/14/2004 3:00:00 PM.

Can anyone point out wht I'm doing wrong with this one?

Thanks,
lq

[Lauren Quantrell]

Steve,
Thanks a million for that. I was trying all manner of cast, convert
and datepart functions but yours is quick and simple. Thanks!
lq

Steve Kass <sk***@drew.edu > wrote in message news:<8h******* **********@news read1.news.pas. earthlink.net>. ..

Lauren,

It sounds like you are using Enterprise Manager. All datetime columns in SQL Server hold both a date and a time, and if you view the
data in Query Analyzer, you should find that your StartDate column holds something like 2004-07-16 12:00:00AM and your StartTime column
holds a value like 1899-12-30 03:00:00PM.

Tools that allows data input will attach a date when a time only is entered into a SQL Server database column, and unfortunately some
tools will attach 1900-01-01 and others will attach 1899-12-30. SQL Server stores the first of these as its zero date, but it stores the
second as -2 (plus whatever fraction of a date the time portion represents, in each case). Enterprise Manager thinks that 1899-12-30 is the
base date, and both attaches it when a bare time is entered and suppresses it when it appears in a datetime to be displayed.

You are doing nothing wrong, but to be safe, you can calculate the time portion explicitly before you add. One way to do this is

StartDate + (StartTime - datediff(day,0, StartTime))

Storing time-only values in SQL Server is tricky, since there is no appropriate type. You need to be careful, and you might want to
consider alternatives, such as storing only the StartDateTime in the database, in which case you can make StartDate and StartTime computed
columns, or calculate them on the fly when you need them.

Steve Kass
Drew University

Lauren Quantrell wrote:

I have two columns in a table:
StartDate DateTime and StartTime DateTime.
The StartDate column holds a value such as 07/16/2004
The StartTime column holds a value such as 3:00:00 PM

I want to be able to add them in a stored procedure.
When I use StartDate + StartTime I get a date two days earlier than expected.
For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns
7/14/2004 3:00:00 PM.

Can anyone point out wht I'm doing wrong with this one?

Thanks,
lq