Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Contribuez SQL Server Discussion :

pour la FAQ : conversion en DATETIME pour heure et date en INT


Sujet :

Contribuez SQL Server

  1. #1
    Expert éminent
    pour la FAQ : conversion en DATETIME pour heure et date en INT
    Sous MS SQL Server 2005, quand on consulte les tables systèmes relatives aux jobs, on a les dates et heures sous format INT (!!!?).
    Voici une façon de récupérer la conversion en DATETIME.

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    select last_run_date 
    	, last_run_time 
    	, convert( datetime, convert( varchar , last_run_date ), 112 ) 
    		+ convert( datetime, stuff( stuff( 
    				replicate ('0', 6 - len (convert(varchar, last_run_time )) ) 
    				+ convert(varchar, last_run_time )
    			 , 3, 0 , ':') , 6, 0 , ':' ) , 108) last_run_datetime
    from msdb.dbo.sysjobservers 
     
    last_run_date last_run_time last_run_datetime
    ------------- ------------- -----------------------
    20080824      235506        2008-08-24 23:55:06.000
    20100517      111326        2010-05-17 11:13:26.000
    20101028      73003         2010-10-28 07:30:03.000
    20101028      14000         2010-10-28 01:40:00.000
    20101028      100           2010-10-28 00:01:00.000
    20101028      0             2010-10-28 00:00:00.000


    C'est sûr que ce n'est pas quelque chose qui requiert beaucoup de compétence, juste de la rigueur, on est d'accord, mais si ça permet à certain de gagner 20 minutes, c'est toujours ça de pris...
    Et en plus, ça fera un endroit où je pourrais retrouver ma bidouille !

    Il doit y avoir plus performant et il est aussi possible de mettre ça dans une fonction, mais pour mon besoin du moment, je n'ai pas besoin d'aller plus loin aujourd'hui.
    les règles du forum - mode d'emploi du forum
    Aucun navigateur ne propose d'extension boule-de-cristal : postez votre code et vos messages d'erreurs. (Rappel : "ça ne marche pas" n'est pas un message d'erreur)
    JE NE RÉPONDS PAS aux questions techniques par message privé.

  2. #2
    Rédacteur

    Plus simple : CAST(CURRENT_TIMESTAMP AS FLOAT)
    donne un réel dont la partie entière est le nombre de jours depuis 1/1/1900 et la partie décimale le nombre de fraction de jours que l'on peut convertir en heure, minute ou seconde décimales....

    Exemple :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    WITH T AS 
    (SELECT CAST(CURRENT_TIMESTAMP AS FLOAT) AS H)
    SELECT CAST(CAST(FLOOR(H) AS DATETIME) AS DATE) AS JOUR, 
           (H - FLOOR(H)) * 1440 AS HEURE_DECIMALE FROM T


    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  3. #3
    Expert éminent
    Ce que tu nous montre ne fonctionne pas directement dans le cas des tables genre msdb.dbo.sysjobservers , car les heures dans le INT ne sont pas des "vraies" secondes, c'est en fait un formatage de type 'HHMISS' stocké dans un INT...
    Si c'est pas pervers...
    les règles du forum - mode d'emploi du forum
    Aucun navigateur ne propose d'extension boule-de-cristal : postez votre code et vos messages d'erreurs. (Rappel : "ça ne marche pas" n'est pas un message d'erreur)
    JE NE RÉPONDS PAS aux questions techniques par message privé.

  4. #4
    Modérateur

    On peut raccourcir ce bout de code :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    replicate ('0', 6 - len (convert(varchar, last_run_time )))
     + convert(varchar, last_run_time)


    En celui-ci :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    right(cast(1000000 + last_run_time as varchar), 6)

  5. #5
    Expert éminent
    Merci Waldar.

    Ça donne donc ça en version 1.1 :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    SELECT last_run_date 
      , last_run_time 
      , convert( datetime, convert( varchar , last_run_date ), 112 ) 
    	+ convert( datetime, stuff( stuff( 
    		RIGHT(cast(1000000 + last_run_time AS varchar), 6)
    		 , 3, 0 , ':') , 6, 0 , ':' ) , 108) last_run_datetime
    FROM msdb.dbo.sysjobservers
    les règles du forum - mode d'emploi du forum
    Aucun navigateur ne propose d'extension boule-de-cristal : postez votre code et vos messages d'erreurs. (Rappel : "ça ne marche pas" n'est pas un message d'erreur)
    JE NE RÉPONDS PAS aux questions techniques par message privé.

  6. #6
    Expert éminent
    Version 1.2
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT last_run_date 
      , last_run_time 
    	, case when last_run_date = 0 and last_run_time = 0 then null else  
    		convert( datetime, convert( varchar , last_run_date ), 112 ) 
    		+ convert( datetime, stuff( stuff( 
    			RIGHT(cast(1000000 + last_run_time AS varchar), 6)
    			 , 3, 0 , ':') , 6, 0 , ':' ) , 108) end last_run_datetime
    FROM msdb.dbo.sysjobservers
    les règles du forum - mode d'emploi du forum
    Aucun navigateur ne propose d'extension boule-de-cristal : postez votre code et vos messages d'erreurs. (Rappel : "ça ne marche pas" n'est pas un message d'erreur)
    JE NE RÉPONDS PAS aux questions techniques par message privé.

  7. #7
    Modérateur

    bonjour,

    une autre approche, avec la manip pour la durée en prime, tirée d'un post de cette semaine http://www.developpez.net/forums/d1064497/bases-donnees/langage-sql/probleme-convert-stuff/

    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
     
    SELECT 
    	S.server_id AS nserveur, 
    	S.last_outcome_message AS message,
    	J.name,  
    	DATEADD(
    		SECOND,
    		(Last_run_time % 10000) % 100,
    		DATEADD(
    			MINUTE,
    			(Last_run_time % 10000) / 100, 
    			DATEADD(
    				HOUR, 
    				Last_run_time / 10000, 
    				CAST(
    					CAST(
    						last_run_date 
    						AS VARCHAR(8)
    					) 
    				AS DATETIME
    				)
    			)
    		)
    	)AS date_heure_derniere_execution, 
    	CASE 
    		WHEN last_run_duration / 10000 < 10 
    			THEN '0' 
    		ELSE '' 
    	END 
    	+ CAST(
    		last_run_duration / 10000 
    		AS VARCHAR(10)
    	) 
    	+ ':' 
    	+ RIGHT(
    		'0'
    		+ CAST(
    			(last_run_duration % 10000) / 100 
    			AS VARCHAR(10)
    		)
    		, 2
    	) 
    	+ ':' 
    	+ RIGHT(
    		'0' 
    		+ CAST(
    			(last_run_duration % 10000) % 100 
    			AS VARCHAR(10)
    		)
    		,2
    	) AS duree
    FROM sysjobservers S
    INNER JOIN sysjobs J ON J.job_id = S.job_id

###raw>template_hook.ano_emploi###