Date stored in table is wrong in SQLite

Question

Can you tell my the date in the SQLite database is taking as 12/3/1899 instead of 12/3/2009. I am inserting correct date while it saving in this format. I can't understand what is the reason.What conversion has to be done in Insert Statement. Can any one help me out.

CREATE TABLE [PIs] ( [PIGUID] GUID PRIMARY KEY NOT NULL, [CompanyGUID] GUID NOT NULL, [No] varCHAR(50) NOT NULL, [Dt] TIMESTAMP NOT NULL, [SupplierLgrGUID] GUID NOT NULL, [SupplierLgrAddressGUID] GUID NOT NULL, [SupplierBillNo] varCHAR(50) NULL, [SupplierBillDt] TIMESTAMP NULL, [CrDays] INTEGER NULL, [DueDt] TIMESTAMP NULL, [Narration] varCHAR(300) NULL, [CreatedDt] TIMESTAMP NOT NULL, [LastEditedDt] TIMESTAMP NOT NULL,)

My Insert Statement is :

INSERT INTO PIs(PIGUID,CompanyGUID,No,Dt,SupplierLgrGUID,SupplierLgrAddressGUID,SupplierBillNo,SupplierBillDt ,CrDays,DueDt,Narration,CreatedDt,LastEditedDt) VALUES('806aeec2-762a-432e-800f-0354df3b7852' ,'375888f5-e1a5-4c75-9154-62ffc83dca97', 'PI/0809/004' ,datetime('8/19/2009 12:44:25 PM'), 'ff376218-c2d9-4e02-86e6-e90c8d5efc43', '7dad4725-2e37-4596-88f4-7b088f0d91c4', '0021', datetime('8/19/2009 12:44:25 PM'), 12, datetime('8/31/2009 12:44:25 PM'), 'narration', datetime('now'),datetime('now'))

I have installed SQLite 3.3.8 and my operating system is Windows Vista.

Any help would be appreciated..

Regards

Asif

Answer 1

The sqlite format for date is the ISO standard ie YYYY-MM-DD and not the one you have used See Sqllite date format

Try SELECT date('now'); to see

Answer 2

This link has some information you should read on using TIMESTAMP and DATETIME for SQLite.

Here's an excerpt:

TIMESTAMP, DATETIME A string type of unlimited length used to store date/time combinations. The required format is 'YYYY-MM-DD HH:MM:SS', anything not following this pattern is ignored.