question

tstrole avatar image
tstrole asked

Oracle date: 16167. I need to converting an Oracle string from YY plus days since January 1st in SQL Server. For example 16167 to June 15, 2016

Is 16167 and Epoch date format?

sql-server-2017
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
declare @OracleDate as varchar(10)
set @OracleDate = '16167'

--[presume default settings for 2 digit years, meaning 49 is 2049, but 50 is 1950]

--determine the start date of the year
--and the number of days to add
select 
 cast('01-01-'+cast(left(@OracleDate,2)as varchar) as date),
 substring(@OracleDate,3, len(@OracleDate))


--put all together in a dateadd() function
-- subtract one from the 'days to add'
select dateadd(
	day, 
	cast(substring(@OracleDate,3, len(@OracleDate)) as int)-1,
	cast('01-01-'+cast(left(@OracleDate,2)as varchar) as date)
	)


          
---------- ----------
2016-01-01 167
(1 row affected)

----------
2016-06-15
(1 row affected)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.