Avatar of dbeayon
dbeayon
 asked on

SQLite datetime conversion question.

I am having problems converting dates in SQLite to dates using the correct YYYY-MM-DD format.  The field ([datetime]) value is 40705.3883778125.  If I use Select date(datetime) as oDate from tblMonitor, oDate = -4601-05-06.  why does it not say 2011-05-06?
Oracle DatabaseDatabases

Avatar of undefined
Last Comment
Kevin Cross

8/22/2022 - Mon
sventhan

-- see it helps

select convert(varchar, [datetime], 110) as oDate from talMonitor

sventhan

or

select cast([datetime] as datetime) from yourtable

http://www.karaszi.com/SQLServer/info_datetime.asp
dbeayon

ASKER
"select cast([datetime] as datetime) from yourtable"  does not change the output.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
dbeayon

ASKER
EDIT

The field name is datetime, the data type is real
sventhan

Kevin Cross

How do you know the date represents '2011-05-06' by the way? i.e., how was this decimal value generated in the first place. When I try this in SQLite, I get '-4601-05-06' with emphasis on fact that it is a negative year. May be some sort of Julian day conversion. If this is a simple numeric representation of date anchored at '1900-01-01' in float format as is the case in MS SQL Server, the result of CAST(...) is '2011-06-13 09:19:15.840'. If you can answer the question of where the value originates, it may help determine how to programmatically convert it back.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dbeayon

ASKER
The information comes from a BDF file that I am querying.  I only have access to the BDF file.  5-06-2011 is when I believe that datetime field started getting populated.  it should be a simple process to display the info in standard date time format.  
Kevin Cross

It should be simple, but each database system stores dates in its own fashion, though most are numerical in nature. For example, some are the number of seconds from 1970-01-01. Others are number of days as a floating point from 1900-01-01. The trick is finding the reference point. The difficulty you are having is that it appears SQLite is storing the values in Julian days, so your number is registering as some date in B.C. To adjust to proper time, once you know the reference you can just add the value you have to that. I can show you how, but again you will need to know the reference point. When I tried with 1900-01-01, I got June 13 and not May 6.
ASKER CERTIFIED SOLUTION
Kevin Cross

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question