Hello group, after a few months, I resumed my schedule, I find myself again fighting with dates. I have a box from which I take the date in EUROPEAN format, therefore dd/mm/YYYY and register it as a stringvalue in the date field.
What error are you getting?
Do you have a sample to review the code/error?
What database are you using?
Column “Rata1” is date field or text field?
Are you trying to save the date as short date like 30/01/2023? or is ConvertToDateTime saving it like SQLDateTime (I guess time will be 00)?
Yes, I should record a short date (EUROPEAN FORMAT dd/mm/YYYY). But I don’t understand why now it’s giving me problems, I managed to enter some dates, but now it’s giving me problems … I don’t understand where I’m wrong.
The field that i used in my database msAccess .mdb, is datetime type.
I don’t understand if the error depends on the format of the date in the search expression …
sql = "SELECT * FROM " +TabellaDiAppartenenza+" WHERE NumeroFattura='" + scadenziario.TextField1.Text.ConvertEncoding(Encodings.WindowsANSI)+"' and Ditta='" + scadenziario.TextField3.text.ConvertEncoding(Encodings.WindowsANSI) +"' and Data=#"+ ConvertToDateTime(scadenziario.textfield2.Text.ConvertEncoding(Encodings.WindowsANSI)).ShortDate+"#"
Var RecordUpdate As RowSet = db.SelectSQL(sql)
In this example, i enter the date 01/07/2022, but the date passed is 2022-07-01.I don’t understand where the error is, whether in the format or the dash bothers me.
Ciao Federico,
the “user” date is a local format
There is no European date format (for example in Germany it is dd.mm.yyyy and so on)
In your db you have to save the SQLDate or SQLDateTime format (yyyy-mm-dd) that have some big advantages like it’s standard for every one, is directly sortable…
When you read a Date Field with this format you can get a DateTime object and show to the user the local standard format or whatever format you want.
This is a classic represantation problem, the real value (the value used by your program) can have a different format for the user, but this is only a view of your original value not the true value (take as example a double value that you write in your code as 3.2 and show to the user as 3,2)
Thanks Antonio, yes, what you are saying is very clear … not being a professional programmer, I was trying to understand why it worked for some entries and now it doesn’t work for me anymore, I think it is related to the date format, but it doesn’t i’m sure, however i’m investigating what the problem is in the sql expression. Thank you.
Looking further into the sql statement, I think it may deal with how I retrieve the data for FROM…it has a name with spaces, I think this may be the problem…now I try to replace “FROM Input Invoice Table” with “FROM INPUTS”. If so, the date field had nothing to do with it, also because I have done many things with dates and up to now we have solved all the problems. As soon as I solve it, I’ll let you know. In the meantime, thanks as always for the support, I don’t feel alone
I’m working for a company (in the Netherlands) that is using MS Access on a daily base.
When using sql statements in VBA we always convert date / datetime to mm-dd-yyyy format.
@Ian_Kennedy
You are absolutely correct.
But the thing is that MS Access doesn’t use standard SQL.
When you create a query in MS Access and look at the created SQL, the date is formatted as #mm-dd-yyyy# or #mm/dd/yyyy# depending on the local settings.
When the database is SQL Server (MS SQL Server Management Studio) the standard SQL date format ‘yyyy-mm-dd’ has to be used outside MS Access, but within MS Access format #mm-dd-yyyy# is still being used.
Through ODBC, all relational DBs to conform to minimal SQL standards. So all them uses it.
Anyway, the best practices for date literals in their native (MS Access Engine) flavor also is the international savvy #YYYY-MM-DD#. Programmers should avoid the problematic dubious #MM/DD/YYYY#
Personally I convert all date/times to SecondsSince1970 which you get for free after parsing a date/time string and creating a new DateTime object. Thus one uses the data storage layer for storing data. Computation and comparison becomes easy too. Finally for presentation I’ve already asked the user what format they would like the date/time to be presented in (unfortunately Locale does not provide access to the system’s settings for this).
But as I also see people writing locale specific confusing codes instead of the international ready ones for the VBA engine side of things, the universal ISO yyyy-mm-dd works too, just surrounded by “#”…
