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.
jlwhite1Asked:
Who is Participating?
 
dbbishopCommented:
Once you adjust, use SELECT CAST(myNumber AS DATETIME) AS theDate
0
 
dbbishopCommented:
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.
0
 
WizillingCommented:
have you tried convert

DECLARE @i int
SET @i=20071015
SELECT CONVERT(datetime,cast(@i AS varchar(8)),108)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
cheeky-monkeyCommented:
You mean in Julian format? If so:

Seelct DateAdd(dd, @julianDayNo, '1900-01-01')
0
 
cheeky-monkeyCommented:
Eeek, obviously "Select".
0
 
jlwhite1Author Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.