Search code examples
sql-serverpeoplesoft

PeopleSoft Query Manager Expression

Using Microsoft SQL Server, I'm trying to format A.CHECK_DT as mmddyyyy - with leading zeros, so january 11th of this year would look like: 01112019. Trouble is everything I try isn't coming out right. The closest I've come to altering the format is:

CONCAT(MONTH((CONVERT(CHAR(10),A.CHECK_DT,101))),DAY((CONVERT(CHAR(10),A.CHECK_DT,101))),YEAR((CONVERT(CHAR(10),A.CHECK_DT,101))))

But that converts the date to 1182019, no leading zeros. Anyone have any ideas?

Solution

  • I figured it out:

    This script will get you a format of MMDDYYYY

    Replace(CONVERT(VARCHAR(10), CAST(A.CHECK_DT AS DATE), 101),'/','')

    (Expression Type = Character, Length 10)