question

rahul930 avatar image
rahul930 asked

function to change the values of a column from yyyymmdd to mm/dd/yyyy

Hey, I would like to create a function that takes the values of a date column (varchar) in yyyymmdd format from a view and changes the values to a mm/dd/yyyy format. I am doing this in order to create a new view from the existing view Should i use dynamic sql here? Can someone show me an example. Also, is it possible to accept multiple values from different columns of the same type and convert them into the same format as shown above. I am using sql server 2012.
sql server 2012functionsvarchar
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sean_Smith avatar image
Sean_Smith answered
SELECT
	CONVERT (VARCHAR (10), CONVERT (DATETIME, '20161231'), 101) AS [mm/dd/yyyy]
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered

Ye gods, why? Why would you do this? Store dates as dates, and then you don't run into these problems, and deal with formatting in the presentation layer.

Apart from anything else, you'll get better response times, save space, and not be tied to a brain-dead middle-endian date format. ISO8601 FTW.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.