newbie int date/time conversion  
Author Message
AMcKay





PostPosted: Fri Dec 03 15:22:23 CST 2004 Top

SQL Server Developer >> newbie int date/time conversion

I'm working with the integer values for next run date and time in
sysjobschedules from msdb. Is there some easy way to convert those values to
a datetime?
--
Thanks,

CGW

SQL Server146  
 
 
Anith





PostPosted: Fri Dec 03 15:22:23 CST 2004 Top

SQL Server Developer >> newbie int date/time conversion There are several ways you can do this. Here are quick two:

--#1
SELECT "name",
CONVERT( DATETIME, CAST( active_start_date AS VARCHAR )
+ SPACE(1) +
REVERSE( STUFF( COALESCE( STUFF( REVERSE(
CAST(active_start_time AS VARCHAR ) ), 3,0, ':' ),
'00:0000' ), 6, 0,':' ) ) )
FROM msdb..sysjobschedules

--#2
SELECT "name", REPLICATE( '0',2-DATALENGTH(
REVERSE( SUBSTRING( REVERSE( CAST(active_end_time AS VARCHAR ) ),
5,2 )))) + SPACE(1) +
REVERSE( SUBSTRING( REVERSE( active_start_time ) +
REPLICATE( '0', 1 ) ,5,2)) + ':' +
REVERSE( SUBSTRING( REVERSE( active_start_time ),3 ,2 ) ) + ':' +
REVERSE( LEFT( REVERSE( active_start_time ),2 ) )
FROM msdb..sysjobschedules ;

--
Anith


 
 
Anith





PostPosted: Fri Dec 03 15:37:38 CST 2004 Top

SQL Server Developer >> newbie int date/time conversion Minor mistake, use active_start_date instead of active_end_time in #2.

--
Anith


 
 
CGW





PostPosted: Fri Dec 03 16:13:04 CST 2004 Top

SQL Server Developer >> newbie int date/time conversion Excellent! Thanks!



> There are several ways you can do this. Here are quick two:
>
> --#1
> SELECT "name",
> CONVERT( DATETIME, CAST( active_start_date AS VARCHAR )
> + SPACE(1) +
> REVERSE( STUFF( COALESCE( STUFF( REVERSE(
> CAST(active_start_time AS VARCHAR ) ), 3,0, ':' ),
> '00:0000' ), 6, 0,':' ) ) )
> FROM msdb..sysjobschedules
>
> --#2
> SELECT "name", REPLICATE( '0',2-DATALENGTH(
> REVERSE( SUBSTRING( REVERSE( CAST(active_end_time AS VARCHAR ) ),
> 5,2 )))) + SPACE(1) +
> REVERSE( SUBSTRING( REVERSE( active_start_time ) +
> REPLICATE( '0', 1 ) ,5,2)) + ':' +
> REVERSE( SUBSTRING( REVERSE( active_start_time ),3 ,2 ) ) + ':' +
> REVERSE( LEFT( REVERSE( active_start_time ),2 ) )
> FROM msdb..sysjobschedules ;
>
> --
> Anith
>
>
>
 
 
Steve





PostPosted: Sat Dec 04 00:58:45 CST 2004 Top

SQL Server Developer >> newbie int date/time conversion CGW,

I get some incorrect results with Anith's queries. In particular, for
start_date 20020416.00 and start_time 500.00, I get NULL for the
datetime in one of them and :5:00 for the date time in the other.

This alternative seems to work:

select
[name],
cast(
rtrim(active_start_date)+space(1) +
rtrim(active_start_time/10000) +':'+
rtrim(active_start_time/100%10000) +':'+
rtrim(active_start_time%100)
as datetime)
from msdb..sysjobschedules

Steve Kass
Drew University




>Excellent! Thanks!
>

>
>
>
>>There are several ways you can do this. Here are quick two:
>>
>>--#1
>>SELECT "name",
>> CONVERT( DATETIME, CAST( active_start_date AS VARCHAR )
>> + SPACE(1) +
>> REVERSE( STUFF( COALESCE( STUFF( REVERSE(
>> CAST(active_start_time AS VARCHAR ) ), 3,0, ':' ),
>> '00:0000' ), 6, 0,':' ) ) )
>> FROM msdb..sysjobschedules
>>
>>--#2
>>SELECT "name", REPLICATE( '0',2-DATALENGTH(
>> REVERSE( SUBSTRING( REVERSE( CAST(active_end_time AS VARCHAR ) ),
>> 5,2 )))) + SPACE(1) +
>> REVERSE( SUBSTRING( REVERSE( active_start_time ) +
>> REPLICATE( '0', 1 ) ,5,2)) + ':' +
>> REVERSE( SUBSTRING( REVERSE( active_start_time ),3 ,2 ) ) + ':' +
>> REVERSE( LEFT( REVERSE( active_start_time ),2 ) )
>> FROM msdb..sysjobschedules ;
>>
>>--
>>Anith
>>
>>
>>
>>
>>