How to set date format in SQL
Discussion
I'm probably googling for the wrong search terms but I cannot get this to work. I am setting up a database in DB4S and I need a date column, preferably in DDMMYY format but YYYY-MM-DD will do. So from my research I need to set 'type' as 'date', so I do this, save it and go to my table, but when I type in something like 1/1/20 it's not converting it to 2020-01-01 or even 010120. What am I doing wrong? What format do I need for the type? I don't need the long format with minutes, seconds etc from 'datetime'. I don't understand why it's not converting it. I've seen it do it automatically on other tables. Thanks.
I see I can do it as varchar but my googling advises against this because it messes up sorting and instead you should use a proper date format.
I see I can do it as varchar but my googling advises against this because it messes up sorting and instead you should use a proper date format.
I don't know your database, but mysql certainly seems to like to store dates as yyyy-mm-dd, and then you can format them when you output the results. I imagine it is to do with sorting.
How are you entering the date? Directly in a db management tool, or via an application? Can you see the structure of the table where you've seen it working as you want it? ( I presume not, as you'd probably have thought of that.)
How are you entering the date? Directly in a db management tool, or via an application? Can you see the structure of the table where you've seen it working as you want it? ( I presume not, as you'd probably have thought of that.)
I'm using DB browser for SQLite. In the database structure > table > modify table, you set your fields and type, like integer, varchar etc. I want to know what to put there to specify that it's a date field. I am currently entering dates as 2020-08-12 manually and it shows them like that with 'date' entered as the 'type' in the database structure, but I'd prefer to have them show in a straight 6 digit DDMMYY format. Obviously I can enter like that, but what do I set in the database structure for it to recognise that as being a date format rather than just a text value or a number value? Are you following? 
hyphen said:
Generally speaking, the US date format is default
I thought fully-backwards date format is default - YYYY-MM-DD rather than the US MM-DD-YYYY. But then I haven't used that many proper databases.Lemming Train said:
Are you following?
Kind of, and I'd normally just say "you can't do that", except for you saying that in some database tables, you can.hyphen said:
Generally speaking, the US date format is default, and then when you show the date anywhere, you use a function to convert it to how you wish to display it.
Unless your performance demands are extreme enough to require it to be static, I would leave it as it is in the database.
What do you mean by static? It is a static date. I have a column for an end date for the record, if applicable. It's not to populate the current date and time, which I think people are getting confused with. In the link below it is far too confusing for me. I don't use the programming sql windows, I only use the front end data grid view. All I want to know is what to set the type to in the configuration panel. 'date' doesn't seem to be working as it shows in bright blue, as if it's expecting additional parameters.Unless your performance demands are extreme enough to require it to be static, I would leave it as it is in the database.
https://imgur.com/a/4NgcfvH
Lemming Train said:
I'm using DB browser for SQLite. In the database structure > table > modify table, you set your fields and type, like integer, varchar etc. I want to know what to put there to specify that it's a date field. I am currently entering dates as 2020-08-12 manually and it shows them like that with 'date' entered as the 'type' in the database structure, but I'd prefer to have them show in a straight 6 digit DDMMYY format. Obviously I can enter like that, but what do I set in the database structure for it to recognise that as being a date format rather than just a text value or a number value? Are you following?
the only format it will 'recognise' as a date is yyyy-mm-dd - for this set a field type of dateto use a different format just set as varchar or int depending on whether you want hyphens in it...
a database is fundamentally a dumb storage of data - think of the field type as a method of validating data / filtering what can be stored...
so int will only allow integers (numbers), varchar will allow the full character set - so 120820 is a valid entry for either an int of length 6 or varchar of length 6
what you do with the data when you pull it out is up to you - so if you set up varchar 6 - then you can enter 120820, but also bobbob - both are 6 long and from the valid characters set - if you want to have the database not allow bobbob you can either set it to int - or you can use your code that adds it to do the validation...
standard though is set the field to date format - it will store as 2020-08-12 and you then extract it into the format you need...
CheesecakeRunner said:
ISO 8601 format is usually the best thing to use in any programming context. Then translate that for display if needed.
Pretty much all databases will use ISO 8601 (actually they use an internal format and just display using ISO 8601 or a subset thereof), such that you can use functions like BETWEEN, DATEDIFF, etc.SQLite is as the name suggest, "lite".
rxtx said:
CheesecakeRunner said:
ISO 8601 format is usually the best thing to use in any programming context. Then translate that for display if needed.
Pretty much all databases will use ISO 8601 (actually they use an internal format and just display using ISO 8601 or a subset thereof), such that you can use functions like BETWEEN, DATEDIFF, etc.SQLite is as the name suggest, "lite".
The data in a date column is not stored with a format (edit/format mask). It's stored in an internal format representing a point in time, but not a display format. The format is in the presentation or application layer in whatever format is defined locally. Two identical dates on the database are always stored in the same internal format regardless of the edit masks used locally. If it could truly be seen in its native form it would probably be indecipherable to humans.
Same goes for loading text data into date columns. You load the text with an edit mask to define where the data sits in the text string, but what gets stored is exactly the same (for the same date) point in time data regardless of what edit mask you used to define it.
It's seems the most logical way given the ambiguity of date formats, especially US to UK.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff