May 3, 2004 at 12:44 pm
The sp_help_job procedure returns the last_run_date and last_run_time in the following format:
20040503 83811
I would need this in this format:
2004-05-03 08:38:11.000
Here is what I came up with
SELECT CAST ( '20040503 ' + STUFF ( STUFF (RIGHT ( '000000' + '83811', 6), 3, 0, ':'), 6,0,':') AS DateTime)
Is there any way I can cast the time string without embedding with ':'.
May 3, 2004 at 4:47 pm
Try using CONVERT instead of CAST. Like SELECT CONVERT ( datetime, '20040503',101)
101 is the format code for the date format you are searching for.
May 4, 2004 at 8:35 am
My question was regarding casting / converting
20040503 83811 into the format of
2004-05-03 08:38:11.000
The solution in my mail with CAST works. I am looking for a better way !
May 4, 2004 at 10:56 am
Well, here's another way, but it's perhaps more convoluted than yours. I think you're stuck with one of these or some other variant that breaks up that '83811'. I don't think there's a way to let SQL Server know that some of those digits are hours, some are minutes, and some are seconds without some sort of parsing.
DECLARE @v Varchar(6) , @t DATETIME
SET @v = '83811'
IF LEN(@v) = 5 SET @v = '0' + @v
SET @t = CAST(LEFT(@v,2) AS FLOAT) /24 + CAST(SUBSTRING(@v,3,2) AS FLOAT)/(24*60) + CAST (RIGHT(@v,2) AS FLOAT)/(24*60*60) SELECT @t, CONVERT(Varchar(20), @t, 108)
Ugly, huh?
May 6, 2004 at 7:32 am
Don't know if the following will help. I got it from Microsoft's web site.
convert(datetime,term_date+" "+
substring(term_time,1,2)+":"+
substring(term_time,3,2)+":"+
substring(term_time,5,2) )
given that term_time has 6 characters (I was thinking about something like 00:00:23 which will only show as 23, which will require some left padding).
Anyway, the URL is http://support.microsoft.com/default.aspx?scid=kb;en-us;69133&Product=sql2k
May 6, 2004 at 9:24 am
if you can change the format of the input date this will work:
select convert(datetime, '20040503 08:38:15.333', 13)
13 is the correct codepage not 101 (mm/dd/yyyy)
cheers
dbgeezer
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply