Date format

[Romio_1968]

I have a table containing a Date/Time field
The system date format is "dd-mm-yyyy"
A weird thing happends

If i am using Now() function to add a date value in the table using SQL, the month and day are inverted

Example 1>

' Add 3 consecutive records in "TDJunction" table
  Dim db2 As DAO.Database
  Dim rs2 As DAO.Recordset
  Set db2 = CurrentDb
  Set rs2 = db2.OpenRecordset("TDJunction")
 
  For i = 1 To 3
    If Me("Rank" & i) <> 0 Then
      rs2.AddNew
      rs2!Title_IDFK = Me.Title_ID
      rs2!ClassCode_FK = Me("ClassCode" & i)
      rs2!Rank = Me("Rank" & i)
      rs2!Timestamp = Now()
      rs2.Update
    End If
  Next i

Example 2

Public scrapTmstp As Date
(...)
'Form_Load event

scrapTmstp = Now()

(...)
'Click Event
' Insert a new record into the "ScrappedInventory" table with the same Inventory_No, Title_ID, and current timestamp
  strSQLscrap = "INSERT INTO ScrappedInventory (Inventory_No, Title_IDFK, ScrapTimestamp, AddTimestamp) " & _
                "VALUES (" & invNo & ", " & titleID & ", #" & scrapTmstp & "#, #" & addTmstp & "#)"
  db.Execute strSQLscrap

In all tables, if i try to use Now(), the d-m inversion occurs

Yet, if I have a control form with default value set to Now(), the value is passed corectly, so the system formats seems to work
it occurs only by using SQL statements

 

[bob fitz]

You may find the following of interest: http://allenbrowne.com/ser-36.html

 

[sonic8]

it occurs only by using SQL statements

You need to explicitly format dates in SQL statements in US or ISO format. See: https://codekabinett.com/rdumps.php?Lang=2&targetDoc=vba-sql-string-tutorial#sql-strings-dates

 

[Pat Hartman]

It's isn't as strange as you think once you understand the problem. The difference is that in the case of setting a control to Now() you are using a date data type to a date data type but when you are writing SQL strings in VBA, you are going from a string data type to a date data type and in that case, you are running afoul of the fact that internally, SQL server assumes that ambiguous dates are in the format of mm/dd/yyyy which is the US standard. THEREFORE, whenever you are working with STRING dates and your default date format is not US standard or something unambiguous such as yyyy/mm/dd, then you must format the date to US date format or to something unambiguous. Be careful of using dd-mmm-yyyy though since I think that there are language issues

.... Format(scrapTmstp, "mm/dd/yyyy") .....

REMEMBER, internally, dates are stored as double precision data types with the integer being the number of days since Dec 30, 1899 (for Jet/ACE and I think Jan 1, 1900 for SQL Server) and the time of day is the decimal part.

 

[theDBguy]

' Insert a new record into the "ScrappedInventory" table with the same Inventory_No, Title_ID, and current timestamp strSQLscrap = "INSERT INTO ScrappedInventory (Inventory_No, Title_IDFK, ScrapTimestamp, AddTimestamp) " & _ "VALUES (" & invNo & ", " & titleID & ", #" & scrapTmstp & "#, #" & addTmstp & "#)" db.Execute strSQLscrap

Just curious, what happens if you try it this way?

' Insert a new record into the "ScrappedInventory" table with the same Inventory_No, Title_ID, and current timestamp
  strSQLscrap = "INSERT INTO ScrappedInventory (Inventory_No, Title_IDFK, ScrapTimestamp, AddTimestamp) " & _
                "VALUES (" & invNo & ", " & titleID & ", Now(), Now())"
  db.Execute strSQLscrap

PS. Not sure which of the two needed the Now() value, I used it on both just for testing.

 

[Pat Hartman]

When you convert Now() to a string in the VBA, it needs to be formatted to US standard. If you pass Now() as a function, you are not converting it to a string and so it doesn't need to be formatted. It is one double precision data type to another and so no formatting happens.

PS, only use Now() if you actually want time of day to be included.