"UK Date" problem!

[Uncle Gizmo]

I appear to have run into the "UK Date" problem!

I have managed to duplicate the problem, and I can recreate the problem consistently. So this indicates that it is probably a design issue.

I have attached a sample database which demonstrates a problem.

The problem is that if I use the Now() function in a normal SQL string, it returns the date correctly in UK format. However if I use the Now() function in a "built" SQL string, that is a string built out of smaller text strings, and fit the Now() function in between two text strings, I add the hash character at each end of the Now() function to indicate to MS Access that the variable is a date. This small change in the way the SQL string is assembled causes it to return the date in the U.S. format. This date is added to the table in U.S. format and completely messes up the database.

'This works as required: (date in UK format)

strSQL = "UPDATE tblUnits SET tblUnits.UnitRepairMoveDate = Now()WHERE (((tblUnits.UnitRepairBatchNo)=44))"

'This works but : (date in US format) Not GOOD!

strSQL1 = "UPDATE tblUnits SET tblUnits.UnitRepairMoveDate = # "
strSQL2 = " #WHERE (((tblUnits.UnitRepairBatchNo)=44))"

strSQL = strSQL1 & Now() & strSQL2

As you can see both bits of code are basically identical, except that the second example includes the hash de-limiter for the date variable. I assume this is having some effect on the date format.


Any help or advice would be greatly appreciated.

 

[neileg]

SQL always wants the date in US format. All dates stored in tables are in US format but when you look at a table in Access you're actually seeing a query based on the table where the local date format is applied.

So...

When you use Now() in the first SQL statement, it naturally returns the date in US format and the SQL is built including this. Where you use Now() outside the statement, you are forcing Access to return the value in English format which will sometimes look like a valid US date and sometimes not and generally screw things up.

Does this make sense?

 

[Uncle Gizmo]

I think I understand!

First SQL statement with the Now() function embedded in it is a complete SQL function so MS Access treats it as such and just updates the table correctly.

However in the second SQL statement the Now() function is actually "outside" of the SQL statement, because my default settings are set to UK, it interprets this Now() function in UK format and then adds it to the SQL statement for execution.

Therefore I get inconsistent dates

have I interpreted your explanation correctly?

And if I have, can you offer a solution to the problem?

 

[RuralGuy]

You also have a spaces problem:

'This works but : (date in US format) Not GOOD!

strSQL1 = "UPDATE tblUnits SET tblUnits.UnitRepairMoveDate = [COLOR=Red][B]# "[/B][/COLOR]
strSQL2 = [COLOR=Red][B]" #WHERE[/B][/COLOR] (((tblUnits.UnitRepairBatchNo)=44))"

strSQL = strSQL1 & Now() & strSQL2

So it should look like:

'This works but : (date in US format) Not GOOD!

strSQL1 = "UPDATE tblUnits SET tblUnits.UnitRepairMoveDate = [B]#"[/B]
strSQL2 = [B]"# WHERE [/B] (((tblUnits.UnitRepairBatchNo)=44))"

strSQL = strSQL1 & Now() & strSQL2

...and you can always use the Format() function.

 

[The_Doc_Man]

Rural Guy makes a good suggestion, but it is even more important to understand something else....

" # " & Format( Now(), "some-user-date-format-string") & " # "

is not a date. It is a string that SQL will interpret as a date literal because of the # marks. But first and foremost it is a STRING.

I have no idea what you will actually see in the resultant string if you use

" # " & Now() & " # "

'cause I don't think that Now() is actually a string. I believe it is a Variant holding a DOUBLE number just waiting to be interpreted as a date. The FORMAT function makes it unequivocal AND you can display whatever you like.

Using Now() directly (not concatenated) in the UPDATE query still provides that Variant as a date but if the targeted field is ALSO a date, that is not a mixed-mode operation, so it works. Using the string (if the target is not a string) fails.

 

[Uncle Gizmo]

Date Format Problem Solved

Thank you very much for your suggestions. I believe I have solved it With your help, my initial tests have so far proven successful.

I had this piece of code which I was using somewhere else, I recalled using it and realize that it might solve this problem.

'Found at:
'http://www.alvechurchdata.co.uk/accsql.htm

Public Function SQLDate(ByVal varLocalDate As Variant) As String
   SQLDate = "#" & Format(varLocalDate, "mm/dd/yyyy hh:mm:ss") & "#"
End Function

'In the attached example
'Remove the #'s from this:
strSQL1 = "UPDATE tblUnits SET tblUnits.UnitRepairMoveDate = "
strSQL2 = " WHERE (((tblUnits.UnitRepairBatchNo)=44))"

'And replace Now() with SQLDate(Now) here:
strSQL = strSQL1 & SQLDate(Now) & strSQL2

 

[Uncle Gizmo]

More Info:

 

[mresann]

Typically, if I'm dealing with international date situations, I locally convert the date to #Jan 04 2005# format to completely unambiguate the date variable.