combine a datetime coulmn and varchar into new Datetime

Question

Post reply

combine a datetime coulmn and varchar into new Datetime

sqlfriend

SSC Guru

Points: 52794
More actions
January 12, 2016 at 12:15 pm

#323476

Could anyone help about the query to combine a datetime and varchar field into a new datetime filed. I am using SQL server 2012.
I need to combine the HearingDate with Hearingtime and makes into a new column HearingDateTime.
Currently the dataType for the 3 field is:
HearingDate : Datatime
HearingTime : Varchar(5)
HearingDateTime : Datatime
HearingDate HearingTime
2001-10-06 00:00:00.000 0830
2010-02-22 00:00:00.000 0330
2010-03-08 00:00:00.000 0400
I am using
SELECT
, HearingDate+ CAST(HearingTime as TIME) newdatetime
from mytable
It is not working.
Thanks for any help

Viewing 10 posts - 1 through 10 (of 10 total)

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

declare @HearingDate datetime = '20011006'

Declare @HearingTime varchar(5) = '0830'

select @HearingDate + cast(stuff(@HearingTime,3,0,':') as datetime)

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 2

Phil beat me to it 😉 Here is a slightly different version

😎

USE tempdb;

GO

SET NOCOUNT ON;

;WITH SAMPLE_DATA(HearingDate,HearingTime)

AS (SELECT CONVERT(DATETIME,X.HearingDate,120),HearingTime FROM (VALUES

('2001-10-06 00:00:00.000','0830')

,('2010-02-22 00:00:00.000','0330')

,('2010-03-08 00:00:00.000','0400')

) AS X(HearingDate,HearingTime)

)

SELECT

DATEADD(MINUTE,DATEDIFF(MINUTE,0,CONVERT(TIME(0),STUFF(SD.HearingTime,3,0,':'),0)),SD.HearingDate)

FROM SAMPLE_DATA SD;

Output

2001-10-06 08:30:00.000

2010-02-22 03:30:00.000

2010-03-08 04:00:00.000

sqlfriend SSC Guru Points: 52794 More actions · Answer 3

sqlfriend

SSC Guru

Points: 52794

January 12, 2016 at 12:54 pm

#1851401

This works great, thanks both.

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 4

sqlfriends (1/12/2016)
This works great, thanks both.

You are very welcome and thank you for the feedback.

😎

drew.allen SSC Guru Points: 77014 More actions · Answer 5

Please note that since you don't include AM or PM, the hearing time will be interpreted on a 24-hour clock, so your 0330 will be 3:30 AM.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

sqlfriend SSC Guru Points: 52794 More actions · Answer 6

That is a good reminder.

Unfortunately the original data does not have a pm, am.

drew.allen SSC Guru Points: 77014 More actions · Answer 7

sqlfriends (1/12/2016)
That is a good reminder.
Unfortunately the original data does not have a pm, am.

There are three possibilities:

1) The data is set up with 24-hour times and you're all set.

2) You may be able to determine AM/PM based on the time values, e.g., anything lower than 0800 is PM.

3) You may have to live with ambiguous data.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

sqlfriend SSC Guru Points: 52794 More actions · Answer 8

Thank you, those are good tips.

I checked the data, the distinct ones are not few. So I may have to do some replace to make the time correct.

Thank you!

drew.allen SSC Guru Points: 77014 More actions · Answer 9

sqlfriends (1/14/2016)
Thank you, those are good tips.
I checked the data, the distinct ones are not few. So I may have to do some replace to make the time correct.
Thank you!

You should be able to use a CASE statement.

declare @HearingDate datetime = '20011006'

Declare @HearingTime varchar(5) = '0100'

select @HearingDate + cast(stuff(@HearingTime,3,0,':') + ' ' + CASE WHEN @HearingTime >= '1200' THEN 'PM' WHEN @HearingTime >= '0800' THEN 'AM' ELSE 'PM' END AS DATETIME)

You may need to adjust the time for the AM cutoff.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA