sqlite date issues moving from 6.5 to 6.8

[markbarrett_1]

Hi,

I'm currently moving my application from 6.5 to 6.8 and I've run into the date problems (string was not recognised as a valid datetime). There are a few entries on the forum with no precise answers.

My code is as follows. The error is when I do a dateparse on the field that is a date.

dbcmd.CommandText = "SELECT * FROM brigade"
dbread.Value = dbcmd.ExecuteReader
If dbread.ReadNextRow = True Then
DateFormat("yyyy/mm/dd")
SQLdbdate = DateParse(dbread.GetValue(18))
DateFormat("dd/mm/yyyy")

Suggestion anyone?

Thanks.

MarkB

 

[markbarrett_1]

Hi,

So I've found a work around (thanks to more searching on the forums)...

I've replaced my sql statement from

select * from table

to

select col1, col2, datetime(col3) from table

where col3 is the date formatted column and things seem to be working again.

For lazy programmers such as me, what a pest, but anyways an answer.

Thanks.

Cheers.

M

 

[markbarrett_1]

Hi,

Spoke to early....Whilst the program compiles, the dates are all wrong :-( (basically they are all blank)

I notice on a couple of threads, there is discussion that there is no such data type in sqlite as date. However I've done all my tablework via the sqlite3.exe. You can add a column where its type is date.

Obviously something has changed. If people have suggestions I'm all open.

Thanks in Advance.

Cheers.

M

 

[RandomCoder]

Hi Mark,

First of all I should explain that I'm new to using SQL and so I'm only advising on my own experience of trying to use dates with SQLLite.
After much head scratching I managed to get my program to work and the key to solving the problem was in the way that the date string is formatted.
SQLLite only supports yyyy-mm-dd and nothing else! (so far as I'm aware)

With my program I had a different format already stored in a file name and so had to convert this before storing in the database. Below is my snipet of code...

Private Sub GetFileList
.
.
.
ParsedFileName( 4 ) = [COLOR="RoyalBlue"]ConvertToSQLDate[/COLOR]( ParsedFileName( 4 ) )
SQLCommand.CommandText = "INSERT INTO FileNames VALUES ( '" & i                        & _ [COLOR="SeaGreen"]'Primary Key[/COLOR]
                                                        "' , '" & AlFiles.Item( i )    & _ [COLOR="SeaGreen"]'File Path[/COLOR]
                                                        "' , '" & ParsedFileName( 0 )  & _ [COLOR="Seagreen"]'Machine Number[/COLOR]
                                                        "' , '" & Magazine( 0 )        & _ [COLOR="Seagreen"]'Magazine 0[/COLOR]
                                                        "' , '" & Magazine( 1 )        & _ [COLOR="Seagreen"]'Magazine 1[/COLOR]
                                                        "' , '" & ParsedFileName( 2 )  & _ [COLOR="Seagreen"]'Order Number[/COLOR]
                                                        "' , '" & ParsedFileName( 3 )  & _ [COLOR="Seagreen"]'Batch Number[/COLOR]
                                                        "' , '" & ParsedFileName( 4 )  & _ [COLOR="Seagreen"]'Date[/COLOR]
                                                        "' )"
SQLCommand.ExecuteNonQuery
.
.
.
End Sub

Private Sub [COLOR="royalblue"]ConvertToSQLDate[/COLOR]( DDMMYYYY )

   DateFormat( "dd-mm-yyyy" )
   Ticks = DateParse( DDMMYYYY )
   DateFormat( "yyyy-mm-dd" )
   Return Date( Ticks )

End Sub

So as you can see, I store the ticks value in this table, but then later I use the ticks value to create a date column in another table (as shown below highlighted in red) ....

Private Sub UpdateTable
.
.
.
SQLCommand.CommandText = "INSERT INTO FilterList " & _
                         "SELECT ID , MachineNumber , MagazineNumber0 , MagazineNumber1 , OrderNumber , " & _
                                     "BatchNumber , Date([COLOR="Red"]OrderDate[/COLOR]) " & _
                         "FROM FileNames " & _
                         "ORDER BY MachineNumber , OrderDate"
SQLCommand.ExecuteNonQuery
End Sub

Hope this helps in some way.

Regards,
RandomCoder

 

[markbarrett_1]

Hi Randomcoder,

Thanks for the reply. Your understanding is what I also understand (dates must be in yyyy/mm/dd format etc). And your code is very similar to mine (doing the whole date conversion thing).

In your database table, is the data type for your date column the sqlite type of date (do a sqlite3 databasename, then at the command prompt do a .schema and you can see all your table definitions)?

If that is the case now I'm really confused.

Cheers.

M

 

[Erel]

Did you try to save the 'ticks' value instead of the string?

 

[markbarrett_1]

Hi,

That will probably work, but I will need to dump my database and reload it as I'll need to modify the database to accept a long. The other issue with that is a tick value is not particularly readable (doing debugging via sql statements in the sqlite3 command line is very useful). As an example using the sqlite command line.

sqlite> select * from brigade;
13806|xxxxxxxxxxxxxxxxx|xxxxxxxxxxxxxxxxxxxxx|xxxxxxx|NSW|xxxx|xxxxxxxxxxxxxxxxxxx|xxxxxxx|NSW|xxxx|+61-2-xxxxxxxx|||+61-xxx-xxxxxx|+61-xxx-xxxxxx|+61-xxx-xxxxxx|+61-xxx-xxxxxx|10|2009/05/30||||xxxxx|0|1

(note this is live data and the xxx are removing sensitive data).

sqlite> .schema
CREATE TABLE Brigade (brigadenum char(10), name char(50), street char(50), suburb char(50), state char(10), postcode char(10), poststreet char(50), postsuburb char(50), poststate char(10), postpostcode char(10), phoneshed char(20), phonemobile char(20), phonefax char(20), phonecaptain char(20), phonesenior char(20), phonepresident char(20), phonesecretary char(20), activitynum longint, dbdate date, substreet char(50), subsuburb char(50), substate char(50), abbrev char(20), showordinary int, showactive int);

As you can see the 19th element (18 in a datareader which starts at 0) is clearly a date that has worked since 5.x. I'm actually starting to wonder if its a bug.

Thoughts?

 

[Erel]

Can you create a small program along with a sample db that demonstrates the problem?

 

[markbarrett_1]

Hi,

Yep not a problem. It shouldn't take long, when I get a few spare minutes. We've had a very big fire close to home and I'm pretty busy at the moment (just about to get some sleep and back on the fireground in 6 hours time).

Cheers.

M

 

[markbarrett_1]

Hi,

Very basic program to demonstrate the problem. There is a single table called sample, with a single column in it, with a single record that has the date of 18/12/2009 (in the database its stored as 2009/12/18), hence the fluffing around to get the date in DD/MM/YYYY format on the screen.

If you run this code from a 6.5 install it all works as desired.

If you run this code from a 6.8 install you get the "string was not recognised as a valid datetime".

Hope this is enough to demonstrate it.

Thanks.

Cheers.

M

 

[Erel]

One comment about the sample code:
- There is no need to call Connection.BeginTransaction before a query. BeginTransaction/EndTransaction are only useful (not mandatory) when doing many 'inserts' one after another. In all other cases it is just a potential problem as sometimes you can forget the EndTransaction and get all kinds of strange behaviors.

SQLite doesn't enforce the column type on the actual data. So the type declaration is usually not too important. However the .Net wrapper uses this declaration and when it encounters a 'date' column it tries to convert it to a .Net DateTime object (and fails).

The simplest solution is to declare the column type as 'Text' instead of 'Date'.
For existing tables you can use a tool like SQLite Expert which is free and supports this operation: SQLite Expert - SQLite Database Administration Tool

 

[markbarrett_1]

Hi Erel,

Thanks for that.

Yep the sample was a quick hack and am aware of the end transaction stuff.

So I presume the .net wrapper is now more strict in the date format in the version that is shippped with 6.8. OK that is fine.

A quick question to round this thread out, if I wanted to stay with the field being a date in the sqlite, what format is the wrapper expecting for the date time (yyyy/mm/dd hh:mm:ss or something like that?).

Thanks.

M

 

[Erel]

It uses the ISO8601 format: ISO 8601 - Wikipedia, the free encyclopedia

 

[RandomCoder]

My appologies for not getting back to you sooner Mark.
I see that Erel has detailed what is happening, and yes I did use a TEXT data type instead of DATE but only after lots of failed attempts at trying to get dates to work .

Glad it is all cleared up for you now though!

Regards,
RandomCoder

 

[markbarrett_1]

Hi,

I've been experimenting to get a definitive answer to this. The reason why I want the date to be represented as a date in the database is I have a number of sql queries that are date dependent and all my logic is in sql queries (the sql engine is very good for searching IMHO and I'm not about re-inventing the wheel).

I think I've got it figured out and now just double checking my work. When I'm happy with the results I'll repost.

Cheers.

M

 

[markbarrett_1]

Hi,

Here is what I've found.

Essentially SQlite uses the - character as a delimiter for the date field and the dates should be in yyyy-mm-dd format. If you are starting out, make sure you insert dates in that format into your sqlite databases that are date type.

Now SQlite won't reject an insert of a date where you put it in yyyy/mm/dd (say from the command line sqlite3.exe). Personally I think this is broken, but that is in the realms of getting the sqlite engine fixed (outside the scope basic4ppc).

In basic4ppc 6.5 or older, the wrapper interfaces to sqlite would happily parse the date in yyyy/mm/dd format from an sql select statement. In basic4ppc 6.8, the wrapper interface to sqlite will reject that data with the error message "string was not recognised as a valid datetime" when trying to parse the date in yyyy/mm/dd format.

However there is more with the sql wrapper in basic4ppc 6.8.

Lets say you insert a date of 2009-12-19 into the database and then select it from sqlite3.exe command line, you will get the date back. Now doing *exactly* the same query via basic4ppc, you get 2009-12-19 12:00:00am. Now if you date parse this to turn this back into another format say dd/mm/yyyy, you'll find you get the answer 0001-01-01. So to get rid of the time component and just get the date you need to do a "select date(columnname) from table" within your basic4ppc program. The result from this will give you just the date and dateformat works as expected.

By inference you can add a time into your date insert (2009-12-19 20:45:00) into an sqlite date field and if you use a select time(columnname) from table it will return the time 20:45:00

Now fortunately the basic4ppc command dateformat will handle both the sqlite format yyyy-mm-dd as well as the australian format for date dd/mm/yyyy.

I hope this clears this up. Now the big question is do I make heaps of changes to code or alter the database to just strings? Dates in the format of yyyy/mm/dd appear searchable on ranges as they are in normal alpha order as 2009/12/23 is string wise after 2009/12/03 etc. I'll be thinking about this for a few days.

Cheers.

M