June 15, 2009 at 1:15 pm
It is possible to convert an YYYYMMDD integer representation of a date directly to a datetime value as follows:
SELECT DATEADD(year, run_date / 10000 - 1900,
DATEADD(month, (run_date / 100) % 100 - 1,
run_date % 100 - 1)) AS run_date3
FROM msdb.dbo.sysjobhistory
However, it is simpler to convert the integer to a character string representation of the date first, as described in previous posts.
February 4, 2016 at 8:13 am
I'm assuming a better option has come available since this was posted, but here is another. It works for dates like 2000-01-01, which in YYMMDD would look like 101.
convert(datetime,right('00000' + ltrim(str(DATEXYZ)),6),12)
In current SQL, can switch datetime <-> date also
The pivot year is 1950 though, which would be a problem with different sorts of dates.
491230 -> 2049-12-30
500101 -> 1950-01-01
If you where doing birthdays you'd want to pivot on a more current year 1915-2014 (1915+35=1950), you could do this:
dateadd(YEAR, -35,convert(date,right('00000' + ltrim(str(BDAYDATE+ 350000)),6),12))
If you wanted something that represented dates from 1990 - 2089 then this would work:
dateadd(YEAR, 40,convert(date,right('00000' + ltrim(str(TRXDATE+ 600000)),6),12))
September 7, 2016 at 10:18 am
moymike (2/4/2016)
I'm assuming a better option has come available since this was posted, but here is another.The pivot year is 1950 though, which would be a problem with different sorts of dates.
491230 -> 2049-12-30
500101 -> 1950-01-01
If you where doing birthdays you'd want to pivot on a more current year 1915-2014 (1915+35=1950), you could do this:
dateadd(YEAR, -35,convert(date,right('00000' + ltrim(str(BDAYDATE+ 350000)),6),12))
Thanks....this works great when trying to get someones birth date in date format from an identity number (in South Africa our ID numbers start with the persons birth date in the format yymmdd, plus another 7 digits)!
Well it works most of the time anyway. Worked fine on my one dataset, but then was bombing out with a "Conversion failed when converting date and/or time from character string." error on another dataset. After investigation it turns out that I have some people born on 29 Feb i.e. on a leap year, so when converting to date after adding the "350000", the "year" in the resultant string changes to a non-leap year, so kicks out the error when it comes across this date.
Just thought I would point this out to potentially save someone else the trouble of having to figure it out the hard way.
Still trying to figure a way around this....open to suggestions?! May have to resort to using a case statement to handle those with a substring(yymmdd,3,4)=0229, but would prefer something less messy. Doing it this way negates the simplicity of using this method and it would almost be easier to just add a 19 or 20 to the front of yymmdd and then convert to date...:unsure:
September 7, 2016 at 10:36 am
rerlston (9/7/2016)
moymike (2/4/2016)
I'm assuming a better option has come available since this was posted, but here is another.The pivot year is 1950 though, which would be a problem with different sorts of dates.
491230 -> 2049-12-30
500101 -> 1950-01-01
If you where doing birthdays you'd want to pivot on a more current year 1915-2014 (1915+35=1950), you could do this:
dateadd(YEAR, -35,convert(date,right('00000' + ltrim(str(BDAYDATE+ 350000)),6),12))
Thanks....this works great when trying to get someones birth date in date format from an identity number (in South Africa our ID numbers start with the persons birth date in the format yymmdd, plus another 7 digits)!
Well it works most of the time anyway. Worked fine on my one dataset, but then was bombing out with a "Conversion failed when converting date and/or time from character string." error on another dataset. After investigation it turns out that I have some people born on 29 Feb i.e. on a leap year, so when converting to date after adding the "350000", the "year" in the resultant string changes to a non-leap year, so kicks out the error when it comes across this date.
Just thought I would point this out to potentially save someone else the trouble of having to figure it out the hard way.
Still trying to figure a way around this....open to suggestions?! May have to resort to using a case statement to handle those with a substring(yymmdd,3,4)=0229, but would prefer something less messy. Doing it this way negates the simplicity of using this method and it would almost be easier to just add a 19 or 20 to the front of yymmdd and then convert to date...:unsure:
maybe...???
DECLARE @idno BIGINT = 1602291234567 --- first six digits are dob
SELECT TRY_CONVERT(date, (CAST(LEFT(@idno,6) AS VARCHAR(6))))
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 7, 2016 at 10:43 am
moymike (2/4/2016)
I'm assuming a better option has come available since this was posted, but here is another. It works for dates like 2000-01-01, which in YYMMDD would look like 101.convert(datetime,right('00000' + ltrim(str(DATEXYZ)),6),12)
In current SQL, can switch datetime <-> date also
The pivot year is 1950 though, which would be a problem with different sorts of dates.
491230 -> 2049-12-30
500101 -> 1950-01-01
If you where doing birthdays you'd want to pivot on a more current year 1915-2014 (1915+35=1950), you could do this:
dateadd(YEAR, -35,convert(date,right('00000' + ltrim(str(BDAYDATE+ 350000)),6),12))
If you wanted something that represented dates from 1990 - 2089 then this would work:
dateadd(YEAR, 40,convert(date,right('00000' + ltrim(str(TRXDATE+ 600000)),6),12))
Or something like this:
with testdata as (
select TestDates from (values (491230),(500101))dt(TestDates)
)
select
TestDates testDatesInt,
cast(cast(TestDates + case when cast(left(TestDates,2) as int) > 49 then 19000000 else 20000000 end as varchar(8)) as datetime)
from
testdata;
September 7, 2016 at 11:46 am
Thanks for the replies. I settled on the following:
convert(date,
case when substring(a.IDNumber,1,2)<=substring(convert(varchar,getdate(),112),3,2) -- when ID birthdate year is less than current year
then convert(varchar,substring(convert(varchar,getdate(),112),1,2))+substring(a.IDNumber,1,6) -- then add current Centuary
else convert(varchar,(substring(convert(varchar,getdate(),112),1,2)-1))+substring(a.IDNumber,1,6) -- otherwise add prior Centuary
end) as BirthDate
This dynamically determines what century to add to the YY value based on the the YY of Getdate(). It does assume though that the dates being converted relate to the 100 years prior to getdate().
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply