T-SQL datetime conversion

问题:

I'm working with a 3rd party database, which (for whatever reason) internal stores a date in the format yyyymm eg 201212 for Dec 2012 as an INT.

Is there a simple way that I may convert this to a SQL Server DateTime type?

I've tried manually converting the above to a char seq of length 6, concatenating this with '01' to get the start of a given month like so:

(CONVERT(char(6), Period) + '01')`

then wrapping this in a datetime conversion:

CONVERT(datetime, (CONVERT(char(6), Period) + '01'))

but this seems to throw;

Conversion failed when converting date and/or time from character string

Can anyone shed any light on what I'm doing wrong?

Thanks! :)

回答1:

You need to use the correct parameter. Here's the msdn chart http://msdn.microsoft.com/en-us/library/ms187928.aspx

In your case, i believe you need to append the '01' and use this

declare @val VARCHAR(8)

SET @val = '201212'

set @val = @val + '01'

print CONVERT(DATETIME, @val, 12)

12 is the ISO format and will take either yymmdd or yyyymmdd

回答2:

This is one way to do it:

Declare @val as int = 20121231;
select DATEADD (YEAR,@val/10000-1900,DATEADD(MONTH,(@val%10000)/100-1,DATEADD(DAY,@val%100-1,0 )))

This is another:

Declare @val as int = 20121231;
    Cast(Cast(@val as char(8)) as date)

The first is good because you won't have any regional setting dependencies. The second is good because it's brief.

回答3:

Convert the integer to a yyyy-mm-dd style string:

select  convert(datetime, 
            left(cast(dt as char(6)),4) + '-' +
            right(cast(dt as char(6)),2) + '-01',
            121) -- 121 = ODBC = yyyy-mm-dd hh:mi:ss.mmm (24h)
from    YourTable

Live example at SQL Fiddle.

回答4:

you can use as below:

Select CAST( CAST(datepart(year,getdate()) AS VARCHAR(4))+ CAST (datepart(MONTH,getdate()) as VARCHAR(2)) AS INT) as YYYYMM

回答5:

This should do the stuff :

DECLARE @Period INTEGER = 201212 
SELECT  CAST( CAST( @Period * 100 + 1 AS CHAR(8) ) AS DATETIME )

回答6:

You can do it with some simple maths, avoiding strings as much as possible:

declare @Start int
set @Start = 201212

select DATEADD(month,((@Start/100)-1970)*12 + (@Start%100-1),'19700101')

That being said, I'd have expected what you originally posted to work, since YYYYMMDD is an unambiguous format.

---

If you're selecting these values from a table that may contain a range of values, only some of which are these values, and you're relying on the WHERE clause to filter out invalid ones before the conversion occurs, you're out of luck - SQL Server has a nasty habit of moving operations around, and can apply filters and conversions in any order it likes.

If this is the case, move the filtering step (to exclude invalid Period values) into a separate query that populates a table variable or temp table, then perform the conversion on those values in a separate query.

回答7:

I've found the following to work quite well. Assuming your INT for the date is in YYYYMM format. First multiply by 100, then add 1 (this results in an INT with a DD of 01) so you now have YYYYMMDD instead of YYYYMM. Then cast to CHAR(8) before casting to DATETIME. Hope it works for you.

DECLARE @SourceDT   INT = 201212
SELECT CAST( CAST((@SourceDT * 100) + 1 AS CHAR(8)) AS DATETIME)