Add Leading Zeros to Date Time expression?

• aaa121

  SSCertifiable

  Points: 5902

  More actions

  March 2, 2010 at 10:24 am

  #138463

  This is my expression, I need to add leading zeros to cater for dates such as 01/01/2010 which appear as 201011.

  Thanks

  "DELETE

  FROM MyTable

  WHERE DATE >= '" + (DT_STR, 4, 1252) DATEPART("yyyy", @[User::CutOffDate]) +

  (DT_STR, 4, 1252) DATEPART("mm", @[User::CutOffDate]) +

  (DT_STR, 4, 1252) DATEPART("dd", @[User::CutOffDate]) + "'"

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  March 2, 2010 at 10:38 am

  #1127273

  I'll have to guess at your question, as you have not stated one ...

  Use something like

  Right('0' + [month as Varchar], 2)

  ---

• aaa121

  SSCertifiable

  Points: 5902

  More actions

  March 2, 2010 at 10:59 am

  #1127288

  Apologies I have been attempting to correct this but cannot get the syntax right. I have tried to add something along the lines of RIGHT('0' + but I just cannot get the syntax in the expression builder right

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  March 2, 2010 at 11:15 am

  #1127300

  This works - just tested it:

  RIGHT("0" + (DT_STR,4,1252)DATEPART("dd",GETDATE()),2)

  ---

• aaa121

  SSCertifiable

  Points: 5902

  More actions

  March 2, 2010 at 11:41 am

  #1127314

  Great, thanks for your help. I was using single quotes for the zero which was causing me an issue.