SQL Dates

[sozzer]

Im in need of some help (again!)

I have a database that runs a fantasy football league. Games are grouped by week and on the main results form a combo box enabling selection of a date together with toggle buttons for leagues change the recordsource of the form as selected by the user.

However, I am working in UK date format and the sql is working in US format. eg If my combo says 06/08/05, it is interpreted as 08/06/05.

I have looked around for help but have had no luck in finding some code to convert the value in my combo to US before using it in an sql statement.

Any help would be greatly appreciated! thanks, ad

 

[RoyVidar]

The only time date format should normally matter, is when feeding it to a string which is afterwards passed to the jet engine, often referred to as dynamic sql. In such cases, an unambiguous format is needed. See for instance this article on how International Dates in Access

Roy-Vidar

 

[PCLewis]

I always use the format string and "dd-mmm-yyyy" when using a date in a query. The advantage is that it's supported in Access, MSSQL and it's mandatory in Oracle (as far as I know it's a default formatpicture which could be changed, but I doubt anyone does). It's just not ambiguous in any way then.

 

[alvechurchdata]

I have a short conversion function which I embed in the SQL:

Function  SQLDate (ByVal  AnyDate As Variant) As String
   '-- See MSDN Q149095 ACC 
   SQLDate = '#' & Format(AnyDate, 'mm/dd/yyyy') & '#'
End Function

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[sozzer]

Thank you both. I converted all the values to strings and i no longer have a problem.

 

[RoyVidar]

To be able to do mathematical operations on dates, they should be stored as date/time, not anything else (opinion), which is why such datatype exist. It can easily be validated, so you don't have to manually or programaticaly check for 15/32/205 ...

alvechurchdata,
sorry, your function, as presented, has two flaws
1 - doesn't compile - single/double quotes
2 - will provide syntax error on some regional settings

I'd again recommend Allen Browne's article for why this

[tt]SQLDate ="#" & Format$(AnyDate, "mm\/dd\/yyyy") & "#"[/tt]

is a better version, or how about an Ansi version "yyyy-mm-dd".

The "dd-mmm-yyyy" version, suffers some of the same, for instance feeding an october date where I reside, gives

"Syntax error in date in query expression '#08-okt-2005#'

- but of course, all of my rant is only worth considering in the very unlikely event that your app is supposed to work outside US, or by users preferring Non-US regional settings...

Roy-Vidar

 

[PHV]

I personally always use the ANSI-like version:
SQLDate = "#" & Format(AnyDate, "yyyy-mm-dd") & "#

 

[alvechurchdata]

1 - doesn't compile - single/double quotes
2 - will provide syntax error on some regional settings

Which version of Access is it failing in? It's worked on my systems for as long as I can remember. I am however only working in UK English versions of Windows and Access.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[RoyVidar]

alvechurchdata,
I'm not trying to be grumpy, but when you've corrected the syntax errors (again, the hint is what kind of quotes you're using), do try switching to for instance German or Norwegian regional settings (Date format dd.mm.yyyy), and try the function. It will, I'm afraid, as PCLewis suggestion, bomb.

The reason why your function will bomb, and the KB article will not, is that yours is formatting (do check out Allen Browne's link, which will explain) an the KB only concatenates. ACC: How to Format Any Date As U.S. Date Regardless of Regional Settings

Roy-Vidar

 

[alvechurchdata]

the hint is what kind of quotes

Sorry about that - you're right they should be double quotes not single. I must have overtyped them when Word replaced the original straight quotes with smart quotes.

As for the German and Norwegian settings I'll plead guilty to that too. I must have read MSDN Q149095 or I wouldn't have quoted it in the function but it was so long ago that I can't remember why I changed the code.

Geoff Franklin

http://www.alvechurchdata.co.uk