COMPARE GETDATE() WITH RUN_DATE COLUMN IN sysjobhistory TABLE

• SQLEngine

  SSCommitted

  Points: 1633

  More actions

  January 27, 2008 at 8:40 am

  #378143

  I am having a problem finding the right STYLE for the getdate() comparison to RUN_DATE column in sysjobhistory table.:hehe: I need to compare the two, and need them to be in the same format:

  for example, this query shows the following format

  select top 1 convert (varchar (8), run_date)

  from dbo.sysjobhistory

  --20080127

  and this query shows a different format:

  select substring (convert (varchar (10), getdate(),126),1,10)

  --2008-01-27

  I am having difficulty finding the right style in CONVERT to get rid og the dashes. Help is appreciated.

• toniupstny

  Hall of Fame

  Points: 3986

  More actions

  January 27, 2008 at 9:00 am

  #772196

  If you look up CAST and CONVERT in the BOL, it has a table of date formats. This one will do what you want.

  select substring (convert (varchar (10), getdate(),112),1,10)

  Toni

• SQLEngine

  SSCommitted

  Points: 1633

  More actions

  January 27, 2008 at 9:17 am

  #772197

  you're right, it did it. I was confusedby the yymmdd format in BOL, because what I needed is yyyymmdd

  Thank you

• antonio.collins

  SSCrazy Eights

  Points: 9600

  More actions

  January 27, 2008 at 9:38 am

  #772200

  just convert getdate() to an int and leave run_date alone:

  cast(convert(char(8),getdate(),112) as int)