locked
Convert a date column to YYYYMMDD format. RRS feed

  • 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 3, 2013 11:34 PM

Answers

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


    Narsimha

    • Marked as answer by BIAnalyst Sunday, March 3, 2013 11:48 PM
    Sunday, March 3, 2013 11:41 PM

All replies

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


    Narsimha

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


    BI Analyst

    • Proposed as answer by Alex Vladimir Wednesday, January 18, 2017 7:34 PM
    Sunday, March 3, 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 4, 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 4, 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
  • DECLARE @date DATE = '3/15/2013'

    SELECT CONVERT(varchar(8),@date,112)

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    • Proposed as answer by Kapil.Kumawat Sunday, June 12, 2016 9:29 AM
    Sunday, June 12, 2016 9:29 AM
  • COBOL, not SQL stores dates as strings. Display formatting is done in a presentation layer. However, the only format allowed in ANSI/ISO standard SQL is "yyyy-mm-dd HH:MM:ss...", Which is a special case of the ISO 8601 standards.

    Sybase has a convert function that was inherited as legacy code by Microsoft when they took over SQL Server. Good SQL programmers do not use it; it was put in to keep COBOL programmers who wanted to see a picture clause happy decades ago. Today, Microsoft has a date data type and a datetime2(n) data type. The latter is there local syntax for what ANSI/ISO SQL calls a timestamp(n).


    --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

    Sunday, June 12, 2016 12:54 PM
  • I know this question is a good 7 years old but I have to ask why people are converting a known, absolute, fixed length output to VARCHAR(anything)??  Don't forget that VARCHAR() carries an extra 2 bytes of overhead to remember the length of the data and that it has to calculate the length of the data, which we already know is going to be exactly "8".  Just use...

     SELECT CONVERT(CHAR(8),SomeDateColumn,112)
       FROM dbo.SomeTable
    ;


    --Jeff Moden

    Tuesday, June 23, 2020 2:22 AM
  • I know this question is a good 7 years old but I have to ask why people are converting a known, absolute, fixed length output to VARCHAR(anything)??  Don't forget that VARCHAR() carries an extra 2 bytes of overhead to remember the length of the data and that it has to calculate the length of the data, which we already know is going to be exactly "8".  Just use...

     SELECT CONVERT(CHAR(8),SomeDateColumn,112)
       FROM dbo.SomeTable
    ;


    --Jeff Moden

    Hi Jeff,

    I know this question is a good 7 years old

    So avoid it :-)

    Moreover, This thread is a question and not a discussion. It was closed already. 

    If you want to discuss something, then you can open new thread and configure it as a discussion instead of a question. If needed then add link(s) to relevant previous threads.

    Don't forget that VARCHAR() carries an extra 2 bytes of overhead to remember the length of the data 

    This is not accurate

    You clearly speak about the format of the row on the disk, which is not relevant to a value on-the-fly which you use using CONVERT.

    In this case the data on the disk is type DATE, which mean that it is always the same on the disk and when we pull it from the disk, regardless if you convert it to VARCHAR or char after you pull the data from the disk/memory.

    If the data on the disk was CHAR or VARCHAR, then your point was relevant.

    On the the disk the data is stored by rows and we have 2 bytes which stores the Number of variable-length columns, if we have any variable length column. Meaning that if we have one or more variable length columns then we have "extra" 2 more bytes. In addition (this is what you meant probably) on the disk we have 2 bytes for each variable length column, which mark the position of the End of the value.

    Pulling the data from the disk keep the structure but once you convert a column or a variable and send it ti the client then you do not keep the original row structure but returning the structure according to the query.

    For example "SELECT C1,C2 From Tbl" might lead to pull from the disk a row with 10 columns, but the query itself when executed only reruns to the client the data of the C1 and C2 (and metadata about the tabular structure of these two columns for example - like the name of the column). 

    So this is totally not relevant for the value which is converted, since these extra bytes are only used for internal needs (like find the position of the value on the disk) and are not returned by the SELECT query to the client. There is no extra size to the size that returned from the server to the client. You can confirm this using Fiddler or use DATALENGTH function.

    DROP TABLE IF EXISTS T
    GO
    create table T(D DATE)
    GO
    INSERT T(D) SELECT CONVERT(DATE, '20210227', 112)
    GO
    
    SELECT D, 
    	CONVERT(VARCHAR(8), D, 112) as C1, DATALENGTH(CONVERT(VARCHAR(8), D, 112)) Size_Of_Data_VARCHAR,
    	CONVERT(CHAR(8)   , D, 112) as C2, DATALENGTH(CONVERT(CHAR(8)   , D, 112)) Size_Of_Data_CHAR
    FROM T
    GO 

     

    In the above example there are no extra bytes. On the disk/cache we are using Date (fixed length data type). 

    The query return to the client String of 8 char in both columns C1 and C2


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    • Edited by pituachMVP Tuesday, June 23, 2020 6:56 AM
    Tuesday, June 23, 2020 6:52 AM
  • Heh... "So avoid it".  That's what I'm trying to get people to do is to "avoid it". ;)

    As for this thread being a "question" and not a "discussion", I have to ask why?  Discussions are how people learn and, IMHO, people are learning the wrong thing because of some of the answers here.  Besides, isn't that what you just did was "discuss" something with me?

    As for opening another thread, that wouldn't help the people that find this one.

    As for your demonstration about not using extra bytes for the VARCHAR() in the conversion formula... I agree that the bytes on disk are not affected because a fixed datatype type is being used.  That's obvious even to a proverbial "first year cadet" in SQL Server (or at least it should be) and so, contrary to your claim,  I "clearly" wasn't speaking about what's on disk. ;)

    To continue on that subject, I agree that it won't impact things much here if VARCHAR() is used because the execution plan is trivial.  If, however, you end up with spooling or anything like it, it matters very much even if the spooling were to not spill to disk and be resident only in memory because memory will still need to carry the data and the metadata for each row so that it actually does know the length of the data.  LEN and DATALENGTH don't find this type of overhead.

    Another thing to consider is getting into the "habit" of doing things right.  If this had gone to disk, then the datatype would be real important because it would mean that an extra 20% of disk space (12 bytes total compared to 10 for each row for this column) would be consumed.  So, especially for newbies, correct datatyping is a VERY good habit to develop early on and they're not going to learn it from the posts on this thread that use VARCHAR() instead of CHAR().

    And, so, to answer the original question, I provided a formula that correctly takes all of that into consideration.

    Heh... and no thread is "closed" (or should be) if there's a possible problem in the answers given. ;)

    p.s.  If "discussions" aren't allowed, then you really need to talk with CELKO on the same subject. ;)


    --Jeff Moden


    • Edited by Jeff Moden Tuesday, June 23, 2020 4:24 PM
    Tuesday, June 23, 2020 3:55 PM