none
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

    Sunday, March 03, 2013 11:34 PM

Answers

All replies

  • DECLARE @v DATE= '3/15/2013'
    
    SELECT CONVERT(VARCHAR(10), @v, 112)


    Narsimha

    • Marked as answer by BIAnalyst Sunday, March 03, 2013 11:48 PM
    Sunday, March 03, 2013 11:41 PM
  • Thank you for the super quick response, Narsimha!


    BI Analyst

    Sunday, March 03, 2013 11:48 PM
  • 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

    Monday, March 04, 2013 8:17 PM
  • 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

    Monday, March 04, 2013 8:26 PM
  • 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.

    Sunday, March 24, 2013 7:32 PM