Concatenate 2 DateTime fields

[atz]

hi,
I have 2 different fields, both datetime, but 1 contains the date, the other the time.
eg
01/01/01 hh:mm:ss and dd/mm/yy 10:00:00

how do i concatenate the date side of the first column, and the time side of the second column into 1 column?

i have tried the & and the + functions, as well as the right and left functions, but i think i dont have the correct syntax.

Within the SQL itself how do i concatenate both the fields into 1 column?

I know there is a simple way of doing this, but as yet am comin up with lots of errors.

Thanx 4 ur help
a
;-)

 

[kabbalah]

u could always convert it to a char variable and use the + operand

 

[ColinM]

You could use datepart to break the time apart.
And you can use cast(dt as varchar(11)) to get the date.

select cast(dt as varchar(11)) +
cast(cast(datepart(hh,tm) as varchar(2)) + ':' +
cast(datepart(mi,tm) as varchar(2)) + ':' +
cast(datepart(ss,tm) as varchar(2)) + '.' +
cast(datepart(ms,tm)as varchar(3)) as datetime)

where dt is the column with the date only and tm is time only

 

[tlbroadbent]

Do this...


Select convert(char(8),DateCol,3) + ' ' + convert(char(10),TimeCol,8) As DateTimeCol Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[opo]

Just looking at the code.
Had similiar problem.
Works fine.
Thanks