Convert Function

Question

I am working on converting a few dates to make them easier to read and when I call upon the convert function to do so, they simply don't convert. When I run the queries they look the same. The code is very simple and short so in theory I wouldn't think there would be anything to prevent it. I can use the datename function and that works but not the convert. Any reason why the convert function isn't working the way it should? Here is the code:

SELECT borrower#,
    location,
    expiration_date,
    last_authentication_date,
    last_cko_date,
    CONVERT(CHAR(8), last_cko_date, 103)
FROM
    borrower

last_cko_date is a small int. The current format that it is being displayed as is: 16911. And what I want the format to be is mm/dd/yyyy

Answer 1

To address the immediate question:

Any reason why the convert function isn't working the way it should?

The CONVERT function works with various types. What kind of conversion to apply is determined principally by the types specified by the first argument (explicitly) and the second argument (implicitly as the type of the expression). The presence of the third argument does not automatically imply that the expression, second parameter, should be treated as a datetime argument, because there are other conversion algorithms that allow you to specify a style as the third argument of CONVERT – not just those between strings and dates/times/datetimes.

So, since last_cko_date is a number (a smallint) and the target type is a string (char(8) specifically), the conversion is made between a number and a string, not between a date and a string, even though you have specified 103, which is a style used for date and time conversion to/from a string.

In order to resolve the problem of getting a date-formatted string out of an integer, therefore, you first need to represent the number as a date or datetime value. The value of 16911 looks to me like an offset from 1970-01-01, in days, so, assuming that, this is what you could do first:

DATEADD(DAY, last_cko_date, '19700101')

That will give you a datetime value corresponding to the integer last_cko_date. Use that expression in the CONVERT function to return the date in the desired style:

CONVERT(char(10), DATEADD(DAY, last_cko_date, '19700101'), 103)

Or, since you actually want mm/dd/yyyy:

CONVERT(char(10), DATEADD(DAY, last_cko_date, '19700101'), 101)

Note also the char(10) instead of char(8). Both styles above return a 10-character string. Truncating it to 8 will remove the last digits of the year, as tpet has correctly noticed.

A possibly better idea, however, would be to omit the conversion in SQL in the first place. Return the date to the client as is and let it do the job of presenting the data in the required format.

Answer 2

Unless you are using SSMS to just present the dates, your presentation layer should handle the part of presenting the date in whatever format you want to display.

I normally use the date-format help to find out the style :-)

SELECT [borrower#], -- use square brackets when you have special characters in the output column name
    location,
    expiration_date,
    last_authentication_date,
    last_cko_date,
    CONVERT(VARCHAR(10), last_cko_date, 101) as last_cko_date_Formatted-- display as MM/DD/YYYY
FROM
    schema.borrower  -- always use schema.TableName