October 3, 2008 at 7:59 am
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.
October 3, 2008 at 8:21 am
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.
October 3, 2008 at 8:29 am
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
October 3, 2008 at 9:01 am
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
October 3, 2008 at 9:08 am
yes time is 24 hrs
October 3, 2008 at 9:36 am
luke,
select cast(cast(20081003 as char(8)) as smalldatetime)
this is not working sql 2000?
October 3, 2008 at 9:38 am
October 3, 2008 at 9:44 am
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.
October 3, 2008 at 10:32 am
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
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
October 3, 2008 at 11:45 am
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
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.
October 3, 2008 at 11:54 am
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
π
October 3, 2008 at 11:58 am
thanks guys , it works
October 4, 2008 at 2:06 am
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))))))
Failing to plan is Planning to fail
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply