J'ai donc cette table qui a des colonnes de date de type int.

last_run_date | last_run_time
   20171116   | 100234

Im essayant de convertir ces deux valeurs en une date / heure à utiliser dans une déclaration datée.

C'est ma déclaration

SELECT 1
FROM V_Jobs_All_Servers vjas
WHERE JobName='DailyReports_xxxx' and Step_Name='xxxx'
and DATEDIFF(hour, Convert(varchar,STUFF(STUFF(STUFF(STUFF(STUFF(cast(
Convert(varchar(100),vjas.last_run_date) +  Convert(varchar(100),vjas.last_run_time) as varchar)
,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':')), Getdate()) <3

Cela fonctionne mais uniquement lorsque la valeur last_run_time est au format d'heure à deux chiffres 101216, mais chaque fois que son heure à un chiffre 91316 échoue avec l'erreur suivante,

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Je suis sur SQL Server 2005

0
user8513344 16 nov. 2017 à 18:19

4 réponses

Meilleure réponse

Si vous obtenez cette valeur de msdb.dbo.sysjobsteps, il existe déjà une fonction intégrée, msdb.dbo.agent_datetime(), pour convertir last_run_date et last_run_time en datetime:

select job_id,
    step_id,
    step_name,
    msdb.dbo.agent_datetime(nullif(last_run_date,0),nullif(last_run_time,0)) as last_run_datetime
from msdb.dbo.sysjobsteps

C'est une fonction non documentée. Cependant, au moins dans ma version de SQL Server (2012), cette fonction a cette définition:

CREATE FUNCTION agent_datetime(@date int, @time int)
RETURNS DATETIME
AS
BEGIN
 RETURN
  (
    CONVERT(DATETIME,
          CONVERT(NVARCHAR(4),@date / 10000) + N'-' + 
          CONVERT(NVARCHAR(2),(@date % 10000)/100)  + N'-' +
          CONVERT(NVARCHAR(2),@date % 100) + N' ' +        
          CONVERT(NVARCHAR(2),@time / 10000) + N':' +        
          CONVERT(NVARCHAR(2),(@time % 10000)/100) + N':' +        
          CONVERT(NVARCHAR(2),@time % 100),
    120)
  )
END
2
Bacon Bits 16 nov. 2017 à 16:43
DECLARE @temp TABLE (last_run_date int, last_run_time int)
INSERT INTO @temp VALUES (20171116, 100234)

SELECT convert(datetime,CAST(last_run_date as varchar))
             + Convert(time, Dateadd(SECOND, Right(last_run_time,2)/1
                                           ,Dateadd(MINUTE, Right(last_run_time,4)/100
                                                          ,Dateadd(hour, Right(last_run_time,6)/10000
                                                                       ,'1900-01-01'
                                                                  )
                                                    )
                                     )
                      ) [DateConverted]
  FROM @temp

Produit la sortie:

DateConverted
2017-11-16 10:02:34.000

Vous pouvez voir comment cela fonctionne en effectuant chaque partie individuellement.

SELECT Dateadd(hour, Right(last_run_time,6)/10000
                   ,'1900-01-01')
FROM @temp

Donne la position des heures.


SELECT Dateadd(MINUTE, Right(last_run_time,4)/100
                     ,Dateadd(hour, Right(last_run_time,6)/10000
                     ,'1900-01-01'))
FROM @temp

Donne la position des heures plus les minutes.


Etc.

0
Zorkolot 16 nov. 2017 à 16:40

Voici une manière moche ...

declare @table table (last_run_date int, last_run_time int)
insert into @table
values
(20171116,100234),
(20171116,91316)

select 
    cast(cast(cast(last_run_date as varchar) as datetime) + ' ' + stuff(stuff(last_run_time,len(last_run_time) - 1,0,':'),len(stuff(last_run_time,len(last_run_time) - 1,0,':')) - 4,0,':') as datetime)
from @table
0
scsimon 16 nov. 2017 à 15:33

Vous êtes en train de compliquer massivement cela, remplissez simplement votre valeur de temps avec un 0 principal et convertissez à partir de là:

declare @t table(last_run_date int, last_run_time int);
insert into @t values(20171116,90234),(20171116,100234);

select last_run_date
      ,last_run_time
      ,convert(datetime,cast(last_run_date as nvarchar(8))
                + ' '
                + stuff(stuff(right('0' + cast(last_run_time as nvarchar(6))
                                   ,6)
                             ,5,0,':')
                       ,3,0,':')
             ,112) as DateTimeData
from @t

Production:

+---------------+---------------+-------------------------+
| last_run_date | last_run_time |      DateTimeData       |
+---------------+---------------+-------------------------+
|      20171116 |        100234 | 2017-11-16 09:02:34.000 |
|      20171116 |        100234 | 2017-11-16 10:02:34.000 |
+---------------+---------------+-------------------------+
1
iamdave 16 nov. 2017 à 15:33
47333260