How can I convert the server date to the English format...

[martindavey]

I am trying to access rows from an MS Access table<br>which is organised within dates.<br><br>I am using a query similar to the following:-<br><br>select * from myTable<br>where myDate &gt;= #FirstDate#<br>and&nbsp;&nbsp;&nbsp;myDate &lt;= #LastDate#<br><br>The trouble is, my data has dates in the standard<br>English format dd/mm/yy<br><br>But I guess the American based server has the format<br>mm/dd/yy<br><br>Is there some way I can ensure that the 'myDate'<br>variable is always in the English format?<br><br>NB. Converting my data to a Non-English format<br>&nbsp;&nbsp;&nbsp;&nbsp;is not an option.

 

[jfriestman]

I can think of three ways to do what you ask:<br><br>1. Format it with your SQL statement.<br>Not sure about the syntax for MS Access JET SQL (I use SQL Server).&nbsp;&nbsp;In SQL Server it's CONVERT (data_type[(length)], expression [, style]).&nbsp;&nbsp;To convert to US English, you would set the style to 101.&nbsp;&nbsp;A cursory check in MS Access didn't turn up with an equivalent statement although I'm pretty sure there's one there.<br><br><br>2. Format it with VBScript.<br>Use the FormatDateTime function to format it:<br>The syntax is FormatDateTime(Date[, NamedFormat]) where NamedFormat is an Enumerator constant (Int value 0 - 4).&nbsp;&nbsp;You probably want to use 1 or 2.<br><br>3. Parse the date parts yourself and put the pieces back together in the format you want. <p> Jeff Friestman<br><a href=mailto: > </a><br><a href=

http://www.brainbench.com/transcript.jsp?pid=61192>

View my Brainbench transcript</a><br>Brainbench 'Most Valuable Professional' for ASP<br>
Brainbench 'Most Valuable Professional' for JavaScript<br>

http://www.brainbench.com

 

[martindavey]

Thanks for your speedy reply.<br><br>Parsing myDate before the query seems to be the most<br>adequate solution.<br><br>Cheers.