• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1872
  • Last Modified:

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.
0
jlwhite1
Asked:
jlwhite1
1 Solution
 
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
 
dbbishopCommented:
Once you adjust, use SELECT CAST(myNumber AS DATETIME) AS theDate
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now