Post reply

Date Conversion to YYYYMMDD

  • September 13, 2010 at 11:09 am

    #146959

    I need help with a date conversion. I want to convert all dates in my query result to YYYYMMDD. They are coming out as MM/DD/YYYY HH:MI:SS:AM. This is my query.

    SELECT CUSTNMBR, DOCDATE AS 'ORIGINAL DOCDATE', POSTDATE AS 'ORIGINAL POSTDATE', DOCNUMBR, CURNCYID, ORTRXAMT,

    CASE WHEN RM20101.ORTRXAMT = RM20101.CURTRXAM THEN 0 ELSE RM20101.CURTRXAM END AS 'Remaining Amt'

    FROM RM20101

    WHERE (DOCDATE > '9/30/2009') AND (CURTRXAM > '0.00') AND (ORTRXAMT >= CURTRXAM)

  • September 13, 2010 at 11:30 am

    #1220212

    the convert finction has an optional parameter when you convert to varchar specifically for formatting things to dates;

    you want to use the 112 format stype:

    SELECT CONVERT(VARCHAR(35),@date,112)

    in your specific example, it's this:

    SELECT

    CUSTNMBR,

    CONVERT(VARCHAR(35),DOCDATE,112) AS 'ORIGINAL DOCDATE',

    CONVERT(VARCHAR(35),POSTDATE,112) AS 'ORIGINAL POSTDATE',

    DOCNUMBR,

    CURNCYID,

    ORTRXAMT,

    CASE

    WHEN RM20101.ORTRXAMT = RM20101.CURTRXAM

    THEN 0

    ELSE RM20101.CURTRXAM

    END AS 'Remaining Amt'

    FROM RM20101

    WHERE (DOCDATE > '9/30/2009')

    AND (CURTRXAM > '0.00')

    AND (ORTRXAMT >= CURTRXAM)[/

    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!

  • September 13, 2010 at 12:15 pm

    #1220241

    Thanks Lowell for you help it worked like a charm.

  • September 13, 2010 at 12:29 pm

    #1220249

    My opinion is that formatting should be done at the client, not the server. Leave it up to the client application to determine how the date should be presented. When I (I'm from Norway) see the date 1/12/2010 I read it as 1st of December 2010, not 12th of January 2010.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply