Date AS INT + Time AS INT = datetime? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Date AS INT + Time AS INT = datetime?

Author

Topic

pr0t0
Starting Member

3 Posts

Posted - 2010-08-27 : 16:10:29

I'm working on populating a SharePoint calendar list from a scheduling db. The db stores the date, and StartTime, and EndTime as type INT in military format except instead of 0900 it's just 900.

SharePoint calendars require start and end times in the datetime format.

Not knowing much about such things, I decided it would be best to concatenate the date column and the time column and then convert that to datetime. To do the first part:

SELECT CAST(CAST(sd.AssignDate AS DECIMAL) * 10000 + sd.StartTime AS varchar(12)) AS 'Start Time''

This allows me to add 4 trailing zeros to the date field so I can add the time. A value of '201008020900' is now returned for what is supposed to be 9am on August 20, 2010.

Then I tried casting that whole thing as datetime like this:

SELECT CAST(CAST(CAST(sd.AssignDate AS DECIMAL) * 10000 + sd.StartTime AS varchar(12)) AS datetime) AS 'Start Time'

Unfortunately, this has not worked. I get an error stating "Conversion failed when converting datetime from character string".

As usual, there is probably a simpler, more elegant way to achieve my goal. Any advice would be greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-27 : 16:54:30

Could you show us some sample data so that we can play with it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pr0t0
Starting Member

3 Posts

Posted - 2010-08-27 : 16:58:52

Well, an example for AssignDate would be 20100802 as an INT. Start time would be 900 as an INT.

Is that what you are looking for?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-27 : 17:08:52

Yes I'll be back shortly with a solution unless someone else beats me to it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-27 : 17:14:02

Here you go:
select CAST(DATEADD(hh, StartTime/100, CONVERT(char(8), AssignDate, 112)) AS datetime) AS 'Start Time'
from yourTable

And here's my test:
declare @t table (AssignDate int, StartTime int)

insert into @t values(20100802, 900)
insert into @t values(20101031, 1300)

select *
from @t

select CAST(DATEADD(hh, StartTime/100, CONVERT(char(8), AssignDate, 112)) AS datetime)
from @t

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-27 : 17:22:25

Here is another way that'll handle minutes (if needed) without impicit conversions:

declare @t table (AssignedDate int, StartTime int)

insert into @t values(20100802, 900)
insert into @t values(20101030, 1300)
insert into @t values(20100210, 2359)
insert into @t values(20100609, 34)


SELECT 
	CAST(CAST(AssignedDate AS VARCHAR(8)) + ' ' + STUFF(RIGHT( '000' + CAST(StartTime AS VARCHAR(4)), 4), 3,0, ':') AS DATETIME)
FROM
	@t

pr0t0
Starting Member

3 Posts

Posted - 2010-08-30 : 08:15:25

GENIUS!!

Thanks be to Tara, the Almighty SQL Goddess; and to Lamprey, the Flowing Fount of Yak Knowledge!

Seriously, thanks to both of you. I'm not sure how I would have arrived at the answer without your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 12:47:57

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources