Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    11

    Unanswered: Formatting Question

    I have an old DB2 app that has date values in 4 fields (i.e. Date1_MO, Date1_DA, Date1_CN, Date1_YR). I have several MSAccess queries that I convert this to a date by doing the following:

    CDate(Date1_MO & "/" & Date1_DA & "/" & Date1_CN & Format(Date1_YR,"00"))

    Piece of cake....however I am struggling with this in MSSQL.

    Mostly I am fighting formatting the Year. As you can see, If I were to concatinate the above values i would come up with something like 3/9/204 for a date of March 9, 2004. (Each field is a numeric value).

    I have gotten this far....

    select CAST(Date1_MO as varchar(2))+ '/' + CAST(Date1_DA as varchar(2))
    + '/' + CAST(Date1_CN as varchar(2))+ CAST(Date1_YR as varchar(2)) as Date1
    From tPrices

    I still need to convert the whole string to a date, but more importantly, I cannot figure out how to get the last element (Year) to format as '04' instead of '4'. I can't concatinate a 0 in front of it for obvious reasons. (Athough I was tempted, just joking)

    I looked through a lot of the T-SQL docs but have come up dry.

    Anyway HELP!!!!!!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try this for the last 2 digits of the year:

    right('0'+CAST(Date1_YR as varchar(2)), 2)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Posts
    11

    Thanks

    Came to the same conclusion about the same time you replied....

    Just playing with the conversion now.

    Thanks for your response...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •