Convert Integer Date to DateTime

• cmalaniz

  SSC Veteran

  Points: 289

  More actions

  February 9, 2009 at 10:13 am

  #197011

  Hi All

  I am trying to convert a date that is in the format of '20080204' to 02/04/2008. Not sure how to do this.

  Thanks

  Craig

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 9, 2009 at 10:19 am

  #940639

  [font="Courier New"]

  SELECT CONVERT(DATETIME,'20080204') --converts cleanly, and displays as '2008-02-04 00:00:00.000'

  SELECT CONVERT(VARCHAR,CONVERT(DATETIME,'20080204'),101) --the desired format '02/04/2008'[/font]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• cmalaniz

  SSC Veteran

  Points: 289

  More actions

  February 10, 2009 at 7:25 am

  #941192

  Hi Lowell

  Thanks for the help. Now I am getting an error that I am not sure how to handle. Here is my query and the error.

  SELECT @@servername AS [Server], J.name AS [Job Name], J.description AS [Job Description], CONVERT(VARCHAR,CONVERT(DATETIME, S.[last_run_date]),101) AS [Last Run Date], S.[last_run_time] AS [Last Run Time], S. [last_outcome_message] AS [Outcome Message]

  FROM MSDB..SYSJOBS J JOIN MSDB..SYSJOBSERVERS S

  ON J.JOB_ID = S.JOB_ID JOIN MSDB..SYSCATEGORIES G

  ON J.category_id = G.category_id

  WHERE G.name = 'CHS SQL Backups'

  ERROR**********************************************************

  Msg 8115, Level 16, State 2, Line 1

  Arithmetic overflow error converting expression to data type datetime.

  (1 row(s) affected)

  ***************************************************************

  Thanks

  Craig

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 10, 2009 at 7:56 am

  #941229

  ok you'd get that eror if there was a weird value in

  can you run this and see what the last run date value is?

  [font="Courier New"]SELECT @@servername AS [Server],

  J.name AS [Job Name],

  J.description AS [Job Description],

  S.[last_run_date],

  --CONVERT(VARCHAR,CONVERT(DATETIME, S.[last_run_date]),101) AS [Last Run Date],

  S.[last_run_time] AS [Last Run Time],

  S. [last_outcome_message] AS [Outcome Message]

  FROM   MSDB..SYSJOBS J JOIN MSDB..SYSJOBSERVERS S

         ON J.JOB_ID = S.JOB_ID  JOIN MSDB..SYSCATEGORIES G

         ON J.category_id = G.category_id

  [/font]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• cmalaniz

  SSC Veteran

  Points: 289

  More actions

  February 10, 2009 at 8:02 am

  #941237

  Hi

  There are several rows returned, they are all in the following format.

  20090209

  Thanks

  Craig

• Christopher Stobbs

  SSC-Insane

  Points: 21098

  More actions

  February 10, 2009 at 8:05 am

  #941241

  check for thats that are leap years but in a year that is not a leap year

  ----------------------------------------------
  Try to learn something about everything and everything about something. - Thomas Henry Huxley

  :w00t:
  Posting Best Practices[/url]
  Numbers / Tally Tables[/url]

  SQL-4-Life

• StarNamer

  SSCrazy Eights

  Points: 8746

  More actions

  February 10, 2009 at 8:06 am

  #941243

  If you look at the definition of the table MSDB..SYSJOBSERVERS, it shows that [Last_run_date] is an int, so you need to convert it

  I also did what you should have done, which is to temporarily change your query to the following to check all values.SELECT

  distinct S.[last_run_date]

  /*

  @@servername AS [Server],

  J.name AS [Job Name],

  J.description AS [Job Description],

  CONVERT(VARCHAR,CONVERT(DATETIME, S.[last_run_date]),101) AS [Last Run Date],

  S.[last_run_time] AS [Last Run Time],

  S. [last_outcome_message] AS [Outcome Message]

  */

  FROM MSDB..SYSJOBS J JOIN MSDB..SYSJOBSERVERS S

  ON J.JOB_ID = S.JOB_ID JOIN MSDB..SYSCATEGORIES G

  ON J.category_id = G.category_id

  Thus shows that a valid value for S.[last_run_date] is zero which is not a valid date.

  Hence to get what you want you need to do this.

  SELECT

  @@servername AS [Server],

  J.name AS [Job Name],

  J.description AS [Job Description],

  case s.[last_run_date] when 0 then 'Never'

  else CONVERT(VARCHAR,CONVERT(DATETIME, convert(char(8),S.[last_run_date])),101) end AS [Last Run Date],

  S.[last_run_time] AS [Last Run Time],

  S. [last_outcome_message] AS [Outcome Message]

  FROM MSDB..SYSJOBS J JOIN MSDB..SYSJOBSERVERS S

  ON J.JOB_ID = S.JOB_ID JOIN MSDB..SYSCATEGORIES G

  ON J.category_id = G.category_id

  Derek

• cmalaniz

  SSC Veteran

  Points: 289

  More actions

  February 10, 2009 at 8:58 am

  #941325

  Thanks that works great.