T-SQL datetime conversion
I’m working with a 3rd party database, which (for whatever reason) internal stores a date in the format
Dec 2012 as an
Is there a simple way that I may convert this to a SQL Server
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
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?
7 Solutions collect form web for “T-SQL datetime conversion”
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
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.
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.
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
This should do the stuff :
DECLARE @Period INTEGER = 201212 SELECT CAST( CAST( @Period * 100 + 1 AS CHAR(8) ) AS DATETIME )
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.
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)