1 Topic by Step-in (edited by Step-in 2024-01-13 17:52:45)

  • Registered: 2023-08-17
  • Posts: 32

Topic: Save only Date and not Time

When creating the table, I selected the DATE field type. Also, in the DateTimePicker element, I selected only the date, but when saving the entry to the database, the program always adds the time as well.

Do I need to store the date in a text field? Or maybe I need to save a record using a query?
Please help me to understand these dates.

Post's attachments

Attachment icon PaymentsRegister m.zip 359.08 kb, 28 downloads since 2024-01-13 

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,675

Re: Save only Date and not Time

Hi,
The short answer to your question is 'no, you don't need to store the date in a text field'.
SQLite doesn't have a 'type' for 'date/time' - in MVD, dates and times are actually already stored as text and are then converted by the in-built date / time functions of SQLite at run-time.
What you actually 'see' on screen and what is stored depends on how you have defined it in your data schema.
If you have chosen 'date' in your schema then when you save a record, the date component is stored and the time component defaults to 00:00:000.
If you have chosen 'time' in your schema then when you save a record, the time component is stored and the date component defaults to 1899-12-30 (which is SQLite's minimum date)
If you have chosen 'date/time' in your schema then when you save a record, both the date and the time components are saved.
Have a look at the attached example and the screen shot showing how the SQLite date/time field type is stored.
Derek.

Post's attachments

Attachment icon dates and times.zip 476.7 kb, 42 downloads since 2024-01-13 

  • Registered: 2023-08-17
  • Posts: 32

Re: Save only Date and not Time

derek wrote:

Hi,
The short answer to your question is 'no, you don't need to store the date in a text field'.
SQLite doesn't have a 'type' for 'date/time' - in MVD, dates and times are actually already stored as text and are then converted by the in-built date / time functions of SQLite at run-time.
What you actually 'see' on screen and what is stored depends on how you have defined it in your data schema.
If you have chosen 'date' in your schema then when you save a record, the date component is stored and the time component defaults to 00:00:000.
If you have chosen 'time' in your schema then when you save a record, the time component is stored and the date component defaults to 1899-12-30 (which is SQLite's minimum date)
If you have chosen 'date/time' in your schema then when you save a record, both the date and the time components are saved.
Have a look at the attached example and the screen shot showing how the SQLite date/time field type is stored.
Derek.

I have many different dates in my database. More than 100,000 approx. In order not to accumulate these "00:00:00.000" values (which occupy a certain place) in the database in the future, I would like to understand how to properly organize saving from the DateTimePicker.

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,675

Re: Save only Date and not Time

  sqlexecute('update example set exampledate = date(exampledate)');

removes the 00:00:000