Convert OLE time format to Date/Time

Hi,

I need a query to convert OLE time format to Date/Time (standard format) in MS SQL. Can someone help me please?

Thanks

ezzadinAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
JestersGrindConnect With a Mentor Commented:
Take a look at the function below.  

Greg


CREATE FUNCTION fnConvertOLEToDateTime (@OLEDate DECIMAL(18, 8))
RETURNS DATETIME

AS

BEGIN

DECLARE @OLEString VARCHAR(50),
		@Days INTEGER,
		@FractionOfADay DECIMAL(18,8),
		@Result DATETIME

SET @OLEString = CONVERT(VARCHAR(50), @OLEDate)

SELECT @Days = SUBSTRING(@OLEString, 1, CHARINDEX('.', @OLEString) - 1), 
	@FractionOfADay = SUBSTRING(@OLEString, CHARINDEX('.', @OLEString), 10)

SELECT @Result = DATEADD(ss, (86400 * @FractionOfADay), DATEADD(dd, @Days, '12/30/1899'))

RETURN @Result
		
END

GO

Open in new window

0
 
lcohanConnect With a Mentor Database AnalystCommented:
I believe the link below has all posible SQL date/time conversions explained in examples

http://msdn.microsoft.com/en-us/library/bb677334.aspx
0
 
ezzadinAuthor Commented:
Thanks all.

JestersGrind, the function worked. Thanks
0
All Courses

From novice to tech pro — start learning today.