Have been researching all day and so far not found an acceptable answer and my experimenting with code has yielded nothing.
I've got a database with a particular 2 columns "StartDate" and "LastBackupDate" which seem to be storing their date information as a Julian date (eg. 40953.6017873071). I need to convert this to a standard Gregorian Date (MM-DD-YYYY or DD-MM-YYYY).
I'm pulling back all results from this table at current "Select * FROM xxxxxx WHERE blah blah".
I'm able to convert these dates in no time with Excel, if I export the data to a sheet, but when I pull the data with Convert, I'm unable to get the date converted.
This is part of a SQL query for a webpage. I can't post out the webcode, but I can post the SQL query:
SELECT * FROM ExpandedCustomerView WHERE regloginid = @0 AND (Status='A' OR Status='H')"
I've been experimenting with this:
SELECT CONVERT(varchar(36),[STARTDATE],101)
[StartDate]
,[LastBackupDate]
FROM [CNTD_Accounts].[dbo].[ExpandedCustomerView]
As a way to get this returned appropriately. I've tried formatting of 101, 110 and others to see if we can get the right results. So far, nothing is working for me. I think this has to be fairly simple.
Cast from number to datetime directly..
select cast(40953.6017873071 as datetime)
--
2012-02-16 14:26:34.423
e.g.
cast([StartDate] as datetime)
If the data is a number in a varchar field, cast it twice
cast(cast([StartDate] as float) as datetime)