Validate Date Time value on Sqlite before insert / update
-
as we all know that the correct format for date and time data in sqlite is : 2014-06-18 01:00:00 my question : is there any method to validate date and time data before insert / update on sqlite, maybe when making table, to make sure date and time data is correct, thanks
rahmat1985 wrote:
as we all know that the correct format for date and time data in sqlite is : 2014-06-18 01:00:00
No it isn't. Dates and times should always be stored as
DATETIMEtypes, which are just numeric values. It is only when they are displayed in human readable form that they may look like that, or18/06/2014 01:00:00 PM, or any other format that the user desires. -
You should be storing the data as a datetime TYPE not a string, therefore convert (TryParse) your string input to a datetime and pass it to the database in the correct data type using a paramaterised query. If it fails the tryparse it is invalid
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
You should be storing the data as a datetime TYPE not a string
SQLite does not have a
DATETIMEdatatype. I'd recommend storing it as an integer, as recommend on their website[^].Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
as we all know that the correct format for date and time data in sqlite is : 2014-06-18 01:00:00 my question : is there any method to validate date and time data before insert / update on sqlite, maybe when making table, to make sure date and time data is correct, thanks
-
as we all know that the correct format for date and time data in sqlite is : 2014-06-18 01:00:00 my question : is there any method to validate date and time data before insert / update on sqlite, maybe when making table, to make sure date and time data is correct, thanks
Steps: Choose the data type that it is actually going to be stored as. Take a value and use on of the sqllite functions to convert it to a different type. Take that value and convert it back to the original type. If they are the same then it is valid. So if you start with text, convert it to a number, then convert the number back to text and compare the original text and the final text.
-
Mycroft Holmes wrote:
You should be storing the data as a datetime TYPE not a string
SQLite does not have a
DATETIMEdatatype. I'd recommend storing it as an integer, as recommend on their website[^].Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Eddy Vluggen wrote:
SQLite does not have a
DATETIMEdatatypeNow that I did not know.
Never underestimate the power of human stupidity RAH
-
Mycroft Holmes wrote:
You should be storing the data as a datetime TYPE not a string
SQLite does not have a
DATETIMEdatatype. I'd recommend storing it as an integer, as recommend on their website[^].Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Eddy Vluggen wrote:
SQLite does not have a
DATETIMEdatatypeHard to believe, but true. Poor guys who have to deal with such crap!
-
Eddy Vluggen wrote:
SQLite does not have a
DATETIMEdatatypeHard to believe, but true. Poor guys who have to deal with such crap!
Bernhard Hiller wrote:
Hard to believe, but true. Poor guys who have to deal with such crap!
I prefer this crap over SqlCe, MS Access, XML and Excel as a single-file database. A DateTime is usually merely a double; a boolean is usually merely a bit. SQLite is lightweight; you won't find a XML, GUID nor a Spatial datatype either - because all those types can be derived from what is already there. The only thing that seemed weird is that the bool is associated with a numeric type, and not an integer-type.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Eddy Vluggen wrote:
SQLite does not have a
DATETIMEdatatypeHard to believe, but true. Poor guys who have to deal with such crap!
Bernhard Hiller wrote:
have to deal with such crap
Seems a choice to me.
You'll never get very far if all you do is follow instructions.
-
Bernhard Hiller wrote:
Hard to believe, but true. Poor guys who have to deal with such crap!
I prefer this crap over SqlCe, MS Access, XML and Excel as a single-file database. A DateTime is usually merely a double; a boolean is usually merely a bit. SQLite is lightweight; you won't find a XML, GUID nor a Spatial datatype either - because all those types can be derived from what is already there. The only thing that seemed weird is that the bool is associated with a numeric type, and not an integer-type.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
I'll stick with SqlCe when given a choice; better to have features I don't use than miss features I want. And have you looked at Sql Server LocalDB?
You'll never get very far if all you do is follow instructions.
-
as we all know that the correct format for date and time data in sqlite is : 2014-06-18 01:00:00 my question : is there any method to validate date and time data before insert / update on sqlite, maybe when making table, to make sure date and time data is correct, thanks
rahmat1985 wrote:
as we all know
We don't all know that.
You'll never get very far if all you do is follow instructions.
-
I'll stick with SqlCe when given a choice; better to have features I don't use than miss features I want. And have you looked at Sql Server LocalDB?
You'll never get very far if all you do is follow instructions.
PIEBALDconsult wrote:
And have you looked at Sql Server LocalDB?
I tried, but failed at downloading[^] it.
PIEBALDconsult wrote:
I'll stick with SqlCe when given a choice; better to have features I don't use than miss features I want.
So you'll be replacing all those SqlCe's with Sql-express versions? SqlCe and SQLite are a close match; but I like to run my code as well on my OpenSUSE desktop and there's no SqlCe there :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
PIEBALDconsult wrote:
And have you looked at Sql Server LocalDB?
I tried, but failed at downloading[^] it.
PIEBALDconsult wrote:
I'll stick with SqlCe when given a choice; better to have features I don't use than miss features I want.
So you'll be replacing all those SqlCe's with Sql-express versions? SqlCe and SQLite are a close match; but I like to run my code as well on my OpenSUSE desktop and there's no SqlCe there :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Eddy Vluggen wrote:
replacing all those SqlCe's with Sql-express versions
No. The other way around. :-D I use Express for a few things, but for one of my projects I wanted to be able to have the app on a flash drive and not require a server/service/daemon running. That way it can be carried around and used whereever. So now I allow the app to use Ce or regular Sql Server, whichever the user wants to use. All the tables and SQL statements are the same (including DATEs and GUIDs), only the Provider is different.
You'll never get very far if all you do is follow instructions.
-
Eddy Vluggen wrote:
replacing all those SqlCe's with Sql-express versions
No. The other way around. :-D I use Express for a few things, but for one of my projects I wanted to be able to have the app on a flash drive and not require a server/service/daemon running. That way it can be carried around and used whereever. So now I allow the app to use Ce or regular Sql Server, whichever the user wants to use. All the tables and SQL statements are the same (including DATEs and GUIDs), only the Provider is different.
You'll never get very far if all you do is follow instructions.