jlwhite1
asked on
Convert date number to actual date
I need to extract a date from an SOS accounting program that has stored it (like Access does) in a numerical format. Does SQL have a converstion available similar to CVDate? Using Access I can convert the number to a date with CVDate([FLD47]-36161), but that doesn't work in SQL.
D B
SQL Server also stores dates internally as a number. 0 represents 1900-01-01. Whole numbers greater than that represent the number of days after 1900-01-01 and negative numbers, the number of days prior. You need to determine what the base date is in your application and adjust it accordingly. For example, today (2007-10-18, is 39371.
have you tried convert
DECLARE @i int
SET @i=20071015
SELECT CONVERT(datetime,cast(@i AS varchar(8)),108)
DECLARE @i int
SET @i=20071015
SELECT CONVERT(datetime,cast(@i AS varchar(8)),108)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You mean in Julian format? If so:
Seelct DateAdd(dd, @julianDayNo, '1900-01-01')
Seelct DateAdd(dd, @julianDayNo, '1900-01-01')
Eeek, obviously "Select".
ASKER
Thanks, that's exactly what I was looking for! My final formula was CAST(CAST(FLD47 - 36163 AS int) AS Datetime) How nice to have Experts-Exchange to go to for answers!