int to date and time.

Question

int to date and time.

bang.prashant

Ten Centuries

Points: 1100
More actions
October 3, 2008 at 7:59 am

#77099

i have one column having date values like int
20081003
20080326
20081003
20081002
and time values like
70004
210053
20003
200002
How to write select query such that by combining above two column values to smalldatetime.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

This should be fairly straightforward, however I do have 1 question for you. The time portion, that looks like it might be the number of miliseconds or some other value since midnight. IS that correct?

If so you could do something like

select dateadd(ms,210053,cast(cast(20081003 as char(8)) as smalldatetime) )

First, cast the int to a character type, then cast to the small datetime and finally, add the ms to the date.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Garadin One Orange Chip Points: 29613 More actions · Answer 2

The only thing that made sense to me with the time was that it was

70004 7:00:04

210053 21:00:53

20003 2:00:03

200002 20:00:02

Seconds wouldn't work, as there are only 86,400 seconds in a day. Miliseconds sound implausible, as the latest date there would be 210 seconds after the turn of midnight, or 12:03:30

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

This is somewhat convoluted, but it should work... though I don't think it would scale terribly well, it should get you moving in the right direction...

create table #test (

Date int,

Time int)

INSERT into #test

SELECT 20081003,70004

UNION

SELECT 20080326,210053

UNION

SELECT 20081003,20003

UNION

SELECT 20081002,200002

--unifying the time length and convverting to char types for ease of transition to datetime

select cast(date as varchar(8)) as mydate,

case WHEN len(time) < 6 THEN

CASE WHEN len(time) = 1 THEN '00000' + cast(time as varchar(6))

WHEN len(time) = 2 THEN '0000' + cast(time as varchar(6))

WHEN len(time) = 3 THEN '000' + cast(time as varchar(6))

WHEN len(time) = 4 THEN '00' + cast(time as varchar(6))

WHEN len(time) = 5 THEN '0' + cast(time as varchar(6)) END

ELSE cast(time as varchar(6))END AS mytime

INTO #test2

from #test

select cast(mydate + ' ' + LEFT(mytime, 2) + ':' + substring(mytime, 3,2) + ':' + right(mytime,2) as smalldatetime)

FROM #test2

--cleanup

DROP Table #test2

DROP Table #test

To help us help you read this[/url]For better help with performance problems please read this[/url]

bang.prashant Ten Centuries Points: 1100 More actions · Answer 4

bang.prashant

Ten Centuries

Points: 1100

October 3, 2008 at 9:08 am

#880525

yes time is 24 hrs

bang.prashant Ten Centuries Points: 1100 More actions · Answer 5

luke,

select cast(cast(20081003 as char(8)) as smalldatetime)

this is not working sql 2000?

Luke L SSC-Dedicated Points: 35547 More actions · Answer 6

Works for me on SQL 2000. What error do you receive?

To help us help you read this[/url]For better help with performance problems please read this[/url]

bang.prashant Ten Centuries Points: 1100 More actions · Answer 7

try this ----

select cast(cast(last_run_date as char(8)) as smalldatetime) from msdb.dbo.sysjobservers

in sql 2000?

i m getting following error

Syntax error converting character string to smalldatetime data type.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Here's another way - I'm sure it won't be the last.

DROP table #test

create table #test (

[Date] int,

[Time] int)

INSERT INTO #test

SELECT 20081003,70004

UNION

SELECT 20080326,210053

UNION

SELECT 20081003,20003

UNION

SELECT 20081002,200002

SELECT CONVERT(DATETIME, STUFF(STUFF(CAST([Date] AS CHAR(8)), 5, 0, '-'), 8, 0, '-') + ' ' +

STUFF(STUFF(RIGHT('00000' + CAST([Time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':'), 120) AS NewDateTime

FROM #test

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luke L SSC-Dedicated Points: 35547 More actions · Answer 9

Nice Chris, Stuff is just one of the commands I completely forget about because I never have need for it.

try this ----
select cast(cast(last_run_date as char(8)) as smalldatetime) from msdb.dbo.sysjobservers

I get...

2008-10-03 00:00:00

with sql 2000

No errors. what language setting is set for your desktop, server collation on the database etc.?

That could be what's causing your problems... If so you'd need to use convert instead of cast so that you can specify your regional settings... Check BOL for the syntax.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

How about the following:

declare @timeint int,

@dateint int;

set @dateint = 20081003;

set @timeint = 70004;

select cast(cast(@dateint as char(8)) as datetime) +

dateadd(ss,(cast(substring(right('0' + cast(@timeint as varchar),8),1,2) as int) * (60 * 60)) +

(cast(substring(right('0' + cast(@timeint as varchar),8),3,2) as int) * 60) +

cast(substring(right('0' + cast(@timeint as varchar),8),5,2) as int), 0),

@dateint,

@timeint

😎

bang.prashant Ten Centuries Points: 1100 More actions · Answer 11

bang.prashant

Ten Centuries

Points: 1100

October 3, 2008 at 11:58 am

#880642

thanks guys , it works

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 12

Another approach

declare @timeint int,

@dateint int;

set @dateint = 20081003;

set @timeint = 70004;

select

dateadd(hour,@timeint/10000%100,

(dateadd(minute,@timeint/100%100,

(dateadd(second,@timeint%100,ltrim(@dateint))))))

Madhivanan

Failing to plan is Planning to fail