Date stored in table is wrong in SQLite
Can you tell my date in SQLite database is taking as 12/3/1899 instead of 12/3/2009. I am inserting the correct date when saving in this format. I cannot understand what is the reason. What transformation should be done in the Insert statement. Can anyone help me.
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 as follows:
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.
Hello
Asif
The sqlite format for date is the ISO standard i.e. YYYY-MM-DD, not the one you used See Sqllite Date Format
Try SELECT date ('now'); see
source to share
This link contains some information that you should read using TIMESTAMP and DATETIME for SQLite.
Here's an excerpt:
TIMESTAMP, DATETIME An unlimited length string type used to store combinations of date and time. Required format: "YYYY-MM-DD HH: MM: SS", anything that doesn't match this pattern is ignored.
source to share
I also had problems with this ... If you are using VB.NET here is how to insert TimeStamp, I mean DateTime in SQLlite
DateTime.UtcNow.ToString ("o")
The important part is .ToString ("o") as it formats the date according to ISO 8601
Here is a link that explains everything about the format - http://msdn.microsoft.com/en-us/library/az4se3k1.aspx#Roundtrip
It looks like this when it enters the database. 2010-12-16T02: 09: 55.7870590Z
But after you insert the data, it looks like this.
12/16/2010 2:09:55 AM
Hope this helps someone else.
source to share