Need help combining separate date / time fields

Question

Post reply

Need help combining separate date / time fields

Sector7G

Ten Centuries

Points: 1040
More actions
October 15, 2010 at 8:26 am

#230522

Hello,
I have a table with a date in one field, and time in another. I need to combine these into one field. I have been Googling this problem for a couple of hours. I have read several articles / forums on this subject. I am so close, but I still need a little help. After I combine fields, I need to have the time recorded down to the second.
I started with the query below that I found on line. When I run this query, my date and time fields look good. My time still shows the exact seconds.
select
CONVERT(VARCHAR(12), CAST(date_worked as DATE)) as Field1,
CONVERT(VARCHAR(8), CAST(start_time as TIME)) as Field2
2010-01-08 | 03:25:25
2010-01-08 | 05:24:10
Next I merge them into one field like this. The combined field looks good, and I can still see time down to the exact second.
select
CONVERT(VARCHAR(12), CAST(date_worked as DATE)) + ' ' + CONVERT(VARCHAR(8), CAST(start_time as TIME)) as Field1
2010-01-08 03:25:25
2010-01-08 05:24:10
Now here’s where I run into a problem: When I try to convert that field to a datetime format as shown below, I loose the seconds. Oddly, the query adds milliseconds while it changes the seconds to zeros.
select
cast((CONVERT(VARCHAR(12), CAST(date_worked as DATE)) + ' ' + CONVERT(VARCHAR(8), CAST(start_time as TIME))) as datetime) as Field1
2010-01-08 03:25:00.000
2010-01-08 05:24:00.000
Is there a way query this, and keep the seconds?
In my table, the field date_worked is formatted as date. The field start_time is formatted as time. It seems redundant to cast them again as the same data type, but that’s the only way the query would run.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Oliiii SSCertifiable Points: 5328 More actions · Answer 1

No need to cast so many things, just do it like this:

Select CAST('2010-01-08' + ' ' + '03:25:25' as DATETIME)

WayneS SSC Guru Points: 95460 More actions · Answer 2

Try this out:

declare @test-2 table (MyDate date, MyTime time);

insert into @test-2 values (getdate(), getdate());

select *,

DateAdd(second, DatePart(second, MyTime), DateAdd(minute, DatePart(minute, MyTime), DateAdd(hour, DatePart(hour, MyTime), convert(datetime, MyDate))))

from @test-2;

The date functions are extremely fast... this may be faster than doing string manipulation.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sector7G Ten Centuries Points: 1040 More actions · Answer 3

Oliiii (10/15/2010)
No need to cast so many things, just do it like this:
Select CAST('2010-01-08' + ' ' + '03:25:25' as DATETIME)

Oliii,

Your query above works fine. But when I substitute the field names from my table like this:

Select CAST(date_worked + ' ' + start_time as DATETIME)

I get an error:

Msg 402, Level 16, State 1, Line 2

The data types date and varchar are incompatible in the add operator.

WayneS SSC Guru Points: 95460 More actions · Answer 4

And this is even easier: