Formating A Date


I have an app which stores a date in a Sqlite database. The date is stored in the d.SQLDate format so that I can query between date ranges easily. So the date should look like 2015-01-08. But my customers sometimes forget that and enter the date in this format in a Text Field. I was wondering if there was some code I could write that would pop a msgbox up when the customer does not enter the date in the correct format or even better change their date to the d.SQLDate automatically.

I tried using this code, that reads the database record and tries to reformat the date, but it does not work. It just puts today’s date in the Text Field instead.

[code] Dim d as date

d = (rsC.Field(“Date”).DateValue)

if NOT (d is nil ) Then
end if[/code]

Any suggestions would be appreciated.


Are you trying to get them to consistent type “2015-01-03” (hyphens & all?)

Several options - but most focus on “dont let them make the error to start with”

  1. dont let them type it ( a date picker control of some kind)
  2. if you do left them type it in make one field for year, one for month, one for day - those are easier to edit check
  3. one large field with an edit mask so they HAVe to type it as YYYYMMDD - which you can easily rip apart

Not letting users make the mistake usually is way better than trying to correct it once they have

Use a text field with a mask such as “##/##/####” to force them to enter the date properly. (Remember to prompt them for the format: “MM/DD/YYYY” or whatever.) Then convert it to SQL format:

[code]Dim sqldate as string

sqldate = NthField(TextField.Text,"/",3) + “-” + NthField(TextField.Text,"/",1) + “-” + NthField(TextField.Text,"/",2)

Thanks Norman and Peter for taking the time to help me. I actually do have a date picker of sort. You can select the date from a popup calendar. I guess I should make the customer have to use calendar instead on manually entering the date. That way it will be in the correct format. Thank you for your help.

Really depends on the use case.
If this is a high volume data entry kind of app then making a person use a mouse etc to select a date will probably get you killed. Watched one accounting system vendor do that and the accounts payable staff was nearly doubled because of a few silly UI choices along this line that killed productivity for getting invoices into the system.

If its a once in a while thing they have to select a date its not so bad.

*PS : we eventually wrote a custom front end for the accounting system that avoided the mistakes they made.
And we did use 3 custom editfields so the numeric keypad + tab key was all that was required.
Productivity rebounded.

Moral : know your users & use cases

The user should never enter sqldate format. Let them enter the date “naturally”. In the US, that would be MMDDYY. We also allow them to enter 1, 2 or 4 digits and supply the rest. So if the date is in the current month and year, 1 or 2 digits is sufficient and is the Day value. 4 digits is interpreted as MMDD and the current year is supplied. And the Space bar fills in the current date. We use a mask the formats in the “-” as they type.

The problem with that Tim is that when you search between two dates SQLite needs to have it in the Sqlite format or you can’t search. That is what I was told when I first started the app. My app is very date specific. There are a lot of searches between dates so in all the date fields the date is in the SQLite format.

how the data is saved in the DB and how it gets there are two different things
always save in a consistent for (sqldate)

but your users maybe should be allowed to enter data in a form that is not sqldate IF thats what they are used to from other experiences

your code should / could convert between the two

What Norm said. I should have been more explicit. There is often a difference between the way you store a value and the way you display it. And the way the user enters it may be different that the other two. Yes, always store dates in SQLDate format, but don’t bother the user with that format. Unless, as Norm points out, your users are already used to using it.

Another example would be time. Some users are familiar with military-style 24 hour time, others are used to 12 hour am/pm time. And you may store it in terms of seconds (if it is a separate field) or as SQLDateTime in a DateTime field.

Make it as easy and flexible as possible for the user to enter, then normalize it in the back end for storage and display.

Another possibility is to fill in the day’s date automatically when the program starts but let the user change it if required. Or add a “Today” button that does the same.

Hi guys!

I’ve a webtextfield and a datepicker. The datepicker control returns my date as (for example, today) 28/06/2016 (I live in Italy, so is correct!)

then I use this as parameter
ps.BindType(4, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.Bind(4, NthField(TxtDataIni.Text,"/",3) + “-” + NthField(TxtDataIni.Text,"/",2) + “-” + NthField(TxtDataIni.Text,"/",1))

that turns my date into 2016-06-28 (checked with a proper msgbox)

If I execute the sql query in MS SQL 2008 Enterprise Manager works, but in xojo I cannot get any record.

tried also
ps.BindType(4, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_DATE)

any hint?


Hi Ciro,
you said that it works so you have checked that the db has the record with the right date?

I used three PopupMenus that allows the user to choose the date from:

Year As YYYY
Month As MM
Day As DD

I also have to get a country name and found so many errors that I took the UNESCO list of countries (the only one I found in French), put the country names in a PopupMenu, et voil.
All data are saved in a SQLite Database, so I can make statistics on the number of countries / persons by countries, Age (Oldest / Youngest), etc.
The Pro Bono user discovers that Kosovo does not exists: I open the country.txt file that holds the list of countries, add it (at its alphabetical location) et voil: no need to recompile !

Hi Antonio!

yes the records with proper date exists, I think that the binded parameter is not corrected for the query…

I’ve checked with a msgbox and is in the format YYYY-MM-DD

Strange, tried also adding 00:00 … as datetime field and nothing changes!

By the way, how can I filter with a SQL prepared statement on a MS SQL 2008R2 db records within a range of dates???
Considering that start and end date filters are webtextfield filled by a date picker…

do you have any (other) suggestion???