trying to convert date to format of MM/DD/YY

  • Hello, I have taken over another colleagues sql project and am not very versed in syntax as of yet, but am needing to convert the 'real_date' to a format of MM/DD/YY whereas currently it is MMDDYY. I believe its because he had it set as a varchar, but on what is listed below how can I change this?

    thanks

    (
    SELECT
    DISTINCT drs.*, amount, drs.payee_name,
    bank_account.account_number,
    (substring(cast(cast(drs.check_date as date) as varchar), 6, 2)+substring(cast(cast(drs.check_date as date) as varchar), 9, 2)+substring(cast(cast(drs.check_date as date) as varchar), 1, 4)) as check_date,
    (substring(cast(cast(drs.void_date as date) as varchar), 6, 2)+substring(cast(cast(drs.void_date as date) as varchar), 9, 2)+substring(cast(cast(drs.void_date as date) as varchar), 1, 4)) as void_date,
    CAST(
    CASE
    WHEN CAST(drs.void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)
    THEN 'CN'
    ELSE 'IS'
    END AS VARCHAR) as status,
    CAST(
    CASE
    WHEN CAST(void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)
    THEN (substring(cast(cast(drs.void_date as date) as varchar), 6, 2)+substring(cast(cast(drs.void_date as date) as varchar), 9, 2)+substring(cast(cast(drs.void_date as date) as varchar), 1, 4))
    ELSE (substring(cast(cast(drs.check_date as date) as varchar), 6, 2)+substring(cast(cast(drs.check_date as date) as varchar), 9, 2)+substring(cast(cast(drs.check_date as date) as varchar), 1, 4))
    END AS VARCHAR) AS real_date
    FROM drs_check as drs
    INNER JOIN payee ON payee.id = drs.payee_id
    INNER JOIN bank_account ON bank_account.account_number = '501112215'
    WHERE
    (drs.company_id = 'TMS2')
    AND
    (drs.check_number NOT LIKE 'd%' AND drs.check_number NOT LIKE '%a%')
    AND
    (CAST(drs.void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE) OR drs.check_date = CAST(dateadd(day, -1, getdate()) AS DATE))
    )
    UNION ALL
    (
    SELECT
    DISTINCT drs.*, amount, drs.payee_name,
    bank_account.account_number,
    (substring(cast(cast(drs.check_date as date) as varchar), 6, 2)+substring(cast(cast(drs.check_date as date) as varchar), 9, 2)+substring(cast(cast(drs.check_date as date) as varchar), 1, 4)) as check_date,
    (substring(cast(cast(drs.void_date as date) as varchar), 6, 2)+substring(cast(cast(drs.void_date as date) as varchar), 9, 2)+substring(cast(cast(drs.void_date as date) as varchar), 1, 4)) as void_date,
    CAST(
    CASE
    WHEN CAST(drs.void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)
    THEN 'CN'
    ELSE 'IS'
    END AS VARCHAR) as status,
    CAST(
    CASE
    WHEN CAST(void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)
    THEN (substring(cast(cast(drs.void_date as date) as varchar), 6, 2)+substring(cast(cast(drs.void_date as date) as varchar), 9, 2)+substring(cast(cast(drs.void_date as date) as varchar), 1, 4))
    ELSE (substring(cast(cast(drs.check_date as date) as varchar), 6, 2)+substring(cast(cast(drs.check_date as date) as varchar), 9, 2)+substring(cast(cast(drs.check_date as date) as varchar), 1, 4))
    END AS VARCHAR) AS real_date
    FROM ap_check as drs
    INNER JOIN vendor ON vendor.id = drs.payee_id
    INNER JOIN bank_account ON bank_account.account_number = '501112215'
    WHERE
    (drs.company_id = 'TMS2')
    AND
    (drs.check_number NOT LIKE 'd%' AND drs.check_number NOT LIKE '%a%')
    AND
    (CAST(drs.void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE) OR drs.check_date = CAST(dateadd(day, -1, getdate()) AS DATE))
    )

  • You can use the convert function instead of the cast function and within the function use the style to determined how the date should look like.  Because the date is stored as varchar (by the way dates should be store as dates and not as string, but this is a different story), you'll need to convert it to date and then convert it again to string with the desired format.  Here is a small example:

    declare @ch char(8)
    set @ch = '06/13/17'
    --Converting the string to datetime
    select convert(datetime,@ch,1)
    --Converting the "datetime" to string with the specified style
    select convert(char(8),convert(datetime,@ch,1),3)

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • could you tell me where in my script this would need to be done?

  • Anyplace you need the format returned, use CAST/CONVERT. It's not needed for the joins, just the return values.

  • so I think its just the two parts which end AS real_date, so from below

    WHEN CAST(void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)
    THEN (substring(cast(cast(drs.void_date as date) as varchar), 6, 2)+substring(cast(cast(drs.void_date as date) as varchar), 9, 2)+substring(cast(cast(drs.void_date as date) as varchar), 1, 4))
    ELSE (substring(cast(cast(drs.check_date as date) as varchar), 6, 2)+substring(cast(cast(drs.check_date as date) as varchar), 9, 2)+substring(cast(cast(drs.check_date as date) as varchar), 1, 4))
    END AS VARCHAR) AS real_date

    how would I write this? I'm sorry I'm really not versed at sql and don't see where I need to put this to display the date as MM/DD/YY

    thanks for any help provided

  • You should really provide DDL to get a good answer.
    1. I strongly suspect your dates are acutally datetimes and a poor effort has been made to do comparisons.
    Casting is a bad idea as it is expensive and is not SARGABLE. If you do cast, always specify the length of the string. ie varchar(8) not varchar.
    2. You should really let the front end do the date formatting.

    Here is your first SELECT with the formatting:

    SELECT DISTINCT drs.*, amount, drs.payee_name, bank_account.account_number
        ,CONVERT(char(8), drs.check_date, 1) AS check_date
        ,CONVERT(char(8), drs.void_date, 1) AS void_date
        ,CASE
            WHEN drs.void_date >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -1)
                AND drs.void_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
            THEN 'CN'
            ELSE 'IS'        
        END AS [status]
        ,CASE
            WHEN drs.void_date >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -1)
                AND drs.void_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
            THEN CONVERT(char(8), drs.void_date, 1)
            ELSE CONVERT(char(8), drs.check_date, 1)        
        END AS real_date
    FROM drs_check as drs
        INNER JOIN payee ON payee.id = drs.payee_id
        INNER JOIN bank_account ON bank_account.account_number = '501112215'
    WHERE drs.company_id = 'TMS2'
        AND drs.check_number NOT LIKE 'd%'
        AND drs.check_number NOT LIKE '%a%'
        AND
        (
            (
                drs.void_date >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -1)
                AND drs.void_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
            )
            OR
            (
                drs.check_date >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -1)
                AND drs.check_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
            )
        );

  • rmcguire 28206 - Tuesday, June 13, 2017 10:00 AM

    so I think its just the two parts which end AS real_date, so from below

    WHEN CAST(void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)
    THEN (substring(cast(cast(drs.void_date as date) as varchar), 6, 2)+substring(cast(cast(drs.void_date as date) as varchar), 9, 2)+substring(cast(cast(drs.void_date as date) as varchar), 1, 4))
    ELSE (substring(cast(cast(drs.check_date as date) as varchar), 6, 2)+substring(cast(cast(drs.check_date as date) as varchar), 9, 2)+substring(cast(cast(drs.check_date as date) as varchar), 1, 4))
    END AS VARCHAR) AS real_date

    how would I write this? I'm sorry I'm really not versed at sql and don't see where I need to put this to display the date as MM/DD/YY

    thanks for any help provided

    Your formula is very simple. It's converting the column to date data type using CAST, then to varchar in a YYYY-MM-DD format (default for date data type), then taking the different parts to set them in the desired order.
    All that work can be reduced by using CONVERT which allows format codes. The format code 101 will return any date/time date type in the format MM/DD/YYYY.
    Check the documentation to know how to make it work. https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • can I have someone explain how on my original script this:

    WHEN CAST(drs.void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)

    differs from this

    WHEN CAST(drs.void_date AS DATE) = CAST(dateadd(day, -3, getdate()) AS DATE)

    as far as that dates of the data getting pulled on what would need to be changed on SSCrazy's example above to accomodate for this?

    thanks again for helping me understand this

  • rmcguire 28206 - Tuesday, June 13, 2017 11:55 AM

    can I have someone explain how on my original script this:

    WHEN CAST(drs.void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)

    differs from this

    WHEN CAST(drs.void_date AS DATE) = CAST(dateadd(day, -3, getdate()) AS DATE)

    as far as that dates of the data getting pulled on what would need to be changed on SSCrazy's example above to accomodate for this?

    thanks again for helping me understand this

    Run the following:
    SELECT CAST(dateadd(day, -1, getdate()) AS DATE), CAST(dateadd(day, -3, getdate()) AS DATE)
    The difference should be obvious.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • so for.....

    drs.void_date >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -3)
        AND drs.void_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)

    will return the records with void _date between 3 days before the current date and the current date, correct?

  • rmcguire 28206 - Tuesday, June 13, 2017 2:14 PM

    so for.....

    drs.void_date >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -3)
        AND drs.void_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)

    will return the records with void _date between 3 days before the current date and the current date, correct?

    Yes, but no including the current date.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 11 posts - 1 through 10 (of 10 total)

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