Tuesday, February 14, 2012

Date+Time = Datetime ???

How can you concatenate two date columns .. one contains the date .. other contains the time (ie date is always '1900-01-01' ) ...
I wan t to get a datetime out of these columns .. how do i do it ?Use the following:

select cast(cast(date1 as decimal(20,10))+cast(date2 as decimal(20,10)) as datetime) from table|||Does not Work
Am providing some Sample data .......
Start Date Start Time
2003-06-03 00:00:00.000 1900-01-01 07:00:00.000
2003-06-02 00:00:00.000 1900-01-01 18:25:00.000
2003-06-04 00:00:00.000 1900-01-01 03:35:00.000
2003-06-04 00:00:00.000 1900-01-01 07:00:00.000
2003-06-03 00:00:00.000 1900-01-01 18:45:00.000|||Try This.

select convert(datetime, date1) + convert(datetime, date2)

Hope this helps!

Cheers!|||Thanks everybody ...
finally did it myself

create function concat_date_time(@.d1 datetime,@.d2 datetime)
returns datetime
as
begin
return convert(datetime,substring(convert(varchar(23),@.d1 ,121),1,10)
+substring(convert(varchar(23),@.d2,121),11,len(con vert(varchar(23),@.d2,121))-11),121)
end

No comments:

Post a Comment