CODEWITHSUNDEEP
sqlsql-server
drewwyatt
drewwyatt

Reputation: 6027

How to change date value format to YYYYMMDD format

When creating a sql backup/web viewer for some AS400 data, some of the data had to be scrubbed by hand.

In this process, the dates in the database ended up being stored as strings in 2 formates:

In an effort to preserve the original data, I would like to convert all of the MM/DD/YYYY to YYYYMMDD. Is there a query that would fix this? Something like:

SELECT commentDate from SomeTable
    IF FORMAT(commentDate) LIKE '00/00/0000'
        commentDate = FORMAT(commentDate, '00000000')

Upvotes: 1

Views: 608

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Select convert(varchar(11),cast(CommentDate  as datetime),112)
From tableName

SQL Fiddle Demo

This shows date in yyyymmdd format if CommentDate is a valid datetime value.

Check this link for MSSQL date format styling

Upvotes: 0

user565869
user565869

Reputation:

First, I highly recommend you track down whoever decided to store dates as text and give him a good beating. You're welcome to borrow my time-travelling android, if it helps.

You're almost there. Using MS SQL syntax:

SELECT
    CASE WHEN CommentDate LIKE '[12][0-9][0-9][0-9][0-1][0-9][0-3][0-9]' THEN CONVERT(DATE, CommentDate, 112)
        WHEN CommentDate LIKE '[0-1][0-9]/[0-3][0-9]/[12][0-9][0-9][0-9]' THEN CONVERT(DATE, CommentDate, 101)
        END
FROM
    (SELECT '20140410' AS CommentDate UNION ALL SELECT '04/10/2014') AS X

Upvotes: 1

Related Questions