将int MMDDYYYY转换为日期时间MM-DD-YYYY

I would like to convert int MMDDYYYY (e.g.-5112012) into datetime MM-DD-YYYY and then convert datetime MM-DD-YYYY into YYYY-MM-DD or if the above can be done in one step converting int MMDDYYYY into datetime YYYY-MM-DD?

我想将int MMDDYYYY(例如-5112012)转换为日期时间MM-DD-YYYY,然后将日期时间MM-DD-YYYY转换为YYYY-MM-DD,或者如果上述步骤可以一步完成将MMDDYYYY转换为日期时间YYYY -MM-DD?

For reference, earlier I converted int YYYYMMDD into datetime YYYY-MM-DD using the following syntax:

作为参考,早先我使用以下语法将YYYYMMDD转换为日期时间YYYY-MM-DD:

declare @date int;
set @date = 19900511
select CONVERT(datetime, convert(varchar(8),@date), 103)

3 个解决方案

#1

DECLARE @date INT;
SET @date = 5112012
SELECT CONVERT(DATETIME, LEFT('0' + CONVERT(VARCHAR(8), @date), 2) + '-' + SUBSTRING( '0' + CONVERT(VARCHAR(8), @date), 3, 2) + '-' + RIGHT(@date,4))

That bails the water, now fix the leak; change the storage from int to a proper date or datetime :)

那拯救水,现在修复泄漏;将存储从int更改为正确的日期或日期时间:)

#2

declare @date int
set @date = 19900511
select CONVERT(CHAR(10), CONVERT(datetime, convert(varchar(8),@date), 103), 21)

SQL Fiddle

The result is 1990-05-11

结果是1990-05-11

#3

convert( datetime, 
  case when @MMDDYYYY = 0 then 
    null 
  else 
    substring(convert( varchar(9), 800000000 + @MMDDYYYY ) , 2, 2) 
               + '/' +  substring(
                   convert( varchar(9), 800000000 + @MMDDYYYY ) , 4, 2) 
               + '/' + right(@MMDDYYYY,4) end ) as New_Date

#1

DECLARE @date INT;
SET @date = 5112012
SELECT CONVERT(DATETIME, LEFT('0' + CONVERT(VARCHAR(8), @date), 2) + '-' + SUBSTRING( '0' + CONVERT(VARCHAR(8), @date), 3, 2) + '-' + RIGHT(@date,4))