combine 2 datetime fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

combine 2 datetime fields

Author

Topic

liangtp@hotmail.com
Starting Member

27 Posts

Posted - 2008-08-14 : 05:48:03

Hi guys,

I have two fields, AppointmentDate and AppointmentTime

AppointmentDate, AppointmentTime
2008-08-05 00:00:00.000; 1899-12-30 10:10:00.000
2008-08-05 00:00:00.000; 1899-12-30 17:14:11.403

How shall I write a SQL statement to combine the Date the first field with the Time from the second filed, to produce one single field with date and time together?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 05:49:26

make use of CHARINDEX() function and look for position of space character. look into books online for syntax.Then use LEFT() and SUBSTRING() to get date & time values.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 06:05:44

SELECT AppointmentDate + AppointmentTime + 2
FROM Table1

The usage of +2 is because it seems the data is populated from Access.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-14 : 06:10:00

select AppointmentDate+AppointmentTime-dateadd(day,datediff(day,AppointmentTime,0),0) as new_date_time from table

Madhivanan

Failing to plan is Planning to fail

liangtp@hotmail.com
Starting Member

27 Posts

Posted - 2008-08-14 : 07:16:12

thanks guys for your help.

visakh16, your code is a little bit longer.

Peso, your code works for me.

Madhivanan, i tried your code but result is incorrect. output below:
2008-08-01 10:10:00.000 (date should be 2008-08-05)
2008-08-01 17:14:11.403

Cheers.

liangtp@hotmail.com
Starting Member

27 Posts

Posted - 2008-08-14 : 07:25:05

Madhivanan, small error in your code. (a '+' instead of a '-')
select AppointmentDate+AppointmentTime+dateadd(day,datediff(day,AppointmentTime,0),0) as new_date_time from table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-14 : 07:49:19

quote:
Originally posted by liangtp@hotmail.com

Madhivanan, small error in your code. (a '+' instead of a '-')
select AppointmentDate+AppointmentTime+dateadd(day,datediff(day,AppointmentTime,0),0) as new_date_time from table

Yes it is.

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 07:55:51

quote:
Originally posted by liangtp@hotmail.com

thanks guys for your help.

visakh16, your code is a little bit longer.

Peso, your code works for me.

Madhivanan, i tried your code but result is incorrect. output below:
2008-08-01 10:10:00.000 (date should be 2008-08-05)
2008-08-01 17:14:11.403

Cheers.

i didnt give you any code. it was just a pointer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources