How do I combine a DATE ...and a TIME field so as to get a DATE/TIME FIELD...?

Experts:

My data fields look like this:

calldatestamp      calltimestamp
2007-10-15 00:00:00.000      1900-01-01 08:39:34.000
2007-10-15 00:00:00.000      1900-01-01 08:41:15.000
2007-10-15 00:00:00.000      1900-01-01 08:50:47.000
2007-10-15 00:00:00.000      1900-01-01 09:12:52.000

I need to know how to combine these fields so as to acheive this:

calldatetimestamp
2007-10-15 08:39:34.000
2007-10-15 08:41:15.000
2007-10-15 08:50:47.000
2007-10-15 09:12:52.000

Thank you for your expertise and assistance...!

MikeV
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
SELECT CONVERT(varchar,CallDateStamp, 112) +' ' +convert(char(8), Time, 108)
0
 
James MurrellProduct SpecialistCommented:
A way to merge the columns would be:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Thank you for the quick reply...this solution is what I was seeking...

MikeV
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Patrick MatthewsCommented:
Hello CRXIuser2005,

I see you already accepted an answer.  If your two source columns are already defined as
datetime, then simply adding the two will give you the result you seek.

Regards,

Patrick
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
NIce......Niiiiicccceeeee.

Thanks for the input...your solution is actually the one I needed...simply too....THANKS again for the added info..it is much appreciated!

MikeV
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
hey Patrick...when I used your method...the YEAR is totally thrown off.....2007 is changing to 2115...???

Any ideas...?

MikeV
0
 
PaultheBrokerCommented:
Mike - this shouldn't happen - I thought it might be because of the different start bases of smalldatetime and datetime but this works fine:

declare @date smalldatetime, @time smalldatetime, @datetime datetime
set @date = '20071130'
set @time = '09:56:12:123'
set @datetime = @date + @time
select @datetime

BTW: if you find you have accepted the 'wrong' answer, the only ethical thing to do is to have the question reopened by posting a 'pointless' question in the support forum, so you can reward the correct answer properly.
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Enlight of the problems I'm having with your suggestion...I do think Anees' suggestion is what works best for me.....and is less labor intensive....thanks again for the added input...

MIkeV
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.