DateTime convert in SQL

问题:

I have DateTime saved as Integer in row of table in format 20111111. I want to display it by select to : 2011-11-11.

Please give me some as simpliest solution.

The Datetime is in table and looks like:

Table_name
20111212
20111113
20111212

and i want to display it like

Table_name
2011-12-12
2011-11-13
2011-12-12

MS SQL Server 2012

回答1:

Please try

declare @a int
    set @a ='20111212'

    select stuff(stuff(@a,len(@a)-3,0,'-'),len(@a)-0,0,'-')

    --> 2011-12-12

fiddle demo

回答2:

You can cast it to varchar, then to datetime, then use CONVERT with the proper style:

SELECT CONVERT(VARCHAR(10),CAST(CAST(20111111 AS VARCHAR(10))AS DATETIME), 120)

Demo

回答3:

Try this

CONVERT(varchar, Year(column)) +'_' + CONVERT(varchar, Month(column)) +'_'+ 
  CONVERT(varchar, Date(column))

回答4:

Problem solved by:

CONVERT(date, CAST(dbo.database_name.table_name AS CHAR(12)), 112) AS table_name