As MYSQL_TYPE_DATE

General
1

I have dates that look like this:

20190108
8-jan-2019

These dates as they are when passed as variants or strings seem to fail in MySQL Insert statements.
Is there a generic method to convert these into some standard format that the insert (A prepared statement) will accept?

ie.
dim d as new Date(dtstring)
ps.insert(dtstring) // Fails

ps.Insert(d) might not?

2

Date.SQLDate and Data.SQLDateTime is the preferred format of storing dates and date times in a database. If you’re users are putting them in in various formats then you’ll end up doing a lot of work to evaluate whether it’s a good date or not.

3

They are all good dates … just people seem to have different styles for some reason.

4

but your database should not have different styles… use PARSEDATE to convert to/from SQLDATE/SQLDATETIME formats so what is in the database is always the same format. This allows you to give consumers of the data the option to display it how THEY want without regard to multiple internal formats

5

That’s a problem you’ll have to deal with then. Seriously. You’ll have to convert from the various styles to one that the Date class can parse. You can start with Parse, I guess but it’s going to fail - a lot.

Someone, Kem?, had a class that would try and figure out dates from any given string. Never used it but it might be worthwhile looking at.

6

I’ve been using Parse to convert the date the user enters to a date in format MM/DD/YYYY in the visible field when they move out of that field. This way they see what date the app understands they entered. Otherwise, you can use a Date Picker custom control that forces them to pick a date from a popup.

All dates are stored YYYY-MM-DD with GMTOffset=0.

7

[quote=403394:@Ralph Alvy]I’ve been using Parse to convert the date the user enters to a date in format MM/DD/YYYY in the visible field when they move out of that field. This way they see what date the app understands they entered. Otherwise, you can use a Date Picker custom control that forces them to pick a date from a popup.
[/quote]
Be careful. Some countries enter dates as DD/MM/YYYY. Whenever the month and day that the user enters is <= 12, you’ll need to have a way to check.

8

Yep. So far, my all my apps are used exclusively in the US. Otherwise, I think I’d use a date picker.