Convert a date column to YYYYMMDD format.

Question

Hi Experts,

How can I write a query to convert a date column (ie. 3/15/2013) to the following format?

20130315 

---

BI Analyst

Answer 1

DECLARE @v DATE= '3/15/2013'

SELECT CONVERT(VARCHAR(10), @v, 112)

---

Narsimha

Answer 2

Thank you for the super quick response, Narsimha!

---

BI Analyst

Answer 3

DATE is a temporal data type. It has no format per se. Did some moron put a string in a column? First, find the moron and beat him, and replace all of his code. He is dangerously stupid. 

Next, alter the table so you have a DATE column. Load it with CAST CONVERT(VARCHAR(10), stupid_date, 112)   AS DATE). Alter the table to drop the original stupid date column. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 4

Hi CELKO,

The date format was fine, it is just a date column; no string attached.  I just need to create a column with the YYYYMMDD format.  This allows me to join it to a fact table which has a DateKey column with that format.

Nevertheless, your method will prove handy should I need to convert  a stupid_date created by a moron to a DATE column.

---

BI Analyst

Answer 5

This is a great resource for all things pertaining to dates in SQL Server:

http://www.sql-server-helper.com/tips/date-formats.aspx

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.