locked
Date format in YYYMMDD RRS feed

  • Question

  • Hi, I am not able to get result the date in YYYYMMDD format.

    create table #temp (DTOPD char(10))

    insert into #temp values ('12/30/1899')

    Select convert(varchar(10),DTOPD,120) from #temp

    --Desired output

    18991230

    Thursday, June 14, 2012 4:35 PM

Answers

  • Your code doesn't work for 2 reasons

    1) Your column is not a datetime

    2) You were using the wrong format code.

    Select convert(varchar(10),CAST(DTOPD as datetime),112) 
    from #temp


    Chuck Pedretti | Magenic – North Region | magenic.com

    • Marked as answer by Khan_K Thursday, June 14, 2012 4:45 PM
    Thursday, June 14, 2012 4:40 PM

All replies

  • Select convert(varchar(10),convert(datetime,DTOPD,110),112) from #temp



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Thursday, June 14, 2012 4:44 PM
    • Proposed as answer by Chuck Pedretti Thursday, June 14, 2012 4:46 PM
    Thursday, June 14, 2012 4:38 PM
  • create table #temp (DTOPD datetime)

    insert into #temp values ('12/30/1899')

    Select convert(varchar(10),DTOPD,112) from #temp


    Please Mark as answer if this answers your question Or Mark as helpful if you found this post was helpful. Trilok Negi

    Thursday, June 14, 2012 4:38 PM
  •      SELECT
                         (((datepart(year, TheDate) * 100) + datepart(month, TheDate))
                    * 100) + datepart(day, TheDate) AS [DateKey]

    Thursday, June 14, 2012 4:39 PM
  • Your code doesn't work for 2 reasons

    1) Your column is not a datetime

    2) You were using the wrong format code.

    Select convert(varchar(10),CAST(DTOPD as datetime),112) 
    from #temp


    Chuck Pedretti | Magenic – North Region | magenic.com

    • Marked as answer by Khan_K Thursday, June 14, 2012 4:45 PM
    Thursday, June 14, 2012 4:40 PM
  • Hi Naomi, I am not able to see your script.

    Thursday, June 14, 2012 4:42 PM
  • It's a forum's bug. I see it in Google Chrome OK, but sometimes the last line is cut off in IE. I've added one extra line, can you see it now?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 14, 2012 4:45 PM
  • Thanks.
    Thursday, June 14, 2012 4:45 PM
  • Same problem as in this thread

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/06306463-d9ac-41e6-847a-2e53ee025b61

    Are you able to see my code now?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 14, 2012 6:37 PM
  • Naomi answered with the correct code solution first.

    It's really cool that so many people answer so quickly. This is a great forum!


    Ed Price (a.k.a User Ed), SQL Server Experience Program Manager (Blog, Twitter, Wiki)

    Thursday, June 21, 2012 2:14 AM
  • It is cool!  I read several books about SQL Server in the past 4 years or so, but I didn't use the technology much.  Then, I got myself a copy of SQL Server Express and started reading all the posts on this forum, in 2012, and the C# 'General' forum too.  I learned a ton from the people posting solutions here.  I think books are great for building a foundation initially, but when it comes to solving REAL business problems, the forums are really indispensable.


    Ryan Shuell

    Thursday, August 30, 2012 1:08 PM
  • I agree. Participating in the forums in my preferred way of learning. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, August 30, 2012 1:36 PM