Convert date number to actual date

Posted on 2007-10-18
Last Modified: 2010-05-19
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.
Question by:jlwhite1
    LVL 15

    Expert Comment

    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.
    LVL 13

    Expert Comment

    have you tried convert

    DECLARE @i int
    SET @i=20071015
    SELECT CONVERT(datetime,cast(@i AS varchar(8)),108)
    LVL 15

    Accepted Solution

    Once you adjust, use SELECT CAST(myNumber AS DATETIME) AS theDate
    LVL 3

    Expert Comment

    You mean in Julian format? If so:

    Seelct DateAdd(dd, @julianDayNo, '1900-01-01')
    LVL 3

    Expert Comment

    Eeek, obviously "Select".

    Author Comment

    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!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This article describes some very basic things about SQL Server filegroups.
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how the fundamental information of how to create a table.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now