"Convert" command?

  • Thread starter Thread starter Ricardo Vazquez
  • Start date Start date
R

Ricardo Vazquez

Hi everybody!

Is there a SQL command for MS Access similar to the CONVERT command in
Transact-SQL?
And could you please help me an usage example?

I need to change a DateTime format, something like this:

(this is what I would do in SQLServer):

SELECT distinct(CONVERT(char(11),dateField,105))
from Table1
order by CONVERT(char(11),dateField,105)

Thank you!

Ricardo.
 
hi Ricardo,

Ricardo said:
Is there a SQL command for MS Access similar to the CONVERT command in
Transact-SQL?
Click to expand...
Not SQL, but VBA. Take a look at CDate(), CStr(), CLng, etc in the OH.


mfG
--> stefan <--
 
You need to relay on VBA functions, which can be called directly from within
the JET SQL statement (and if you are within MS ACCESS), without having to
register anything (unlike using C#/Vb.Net with MS SQL Server). You can use
CDate( ), but that highly depends on the PC Setting the query runs onto, to,
silently, prefers dd-mm-yyyy over mm-dd-yyyy. It is even worse, since you
don't really control which PC will ever run your query, if the string uses
local month name, since, say, on some PC, the three letters month's name, if
not in English, the month name may not be understood at all. You can use
MID(string, start, lengthOfSubstring) to 'slice' the original string. As
example, DateSerial( ) is a VBA function which accepts three arguments: the
year, the month and the day (all integers). So, someone can use:

DateSerial( INT(MID( myString, 7, 4)), INT(MID(myString, 4, 2)),
INT(LEFT(myString, 2)) )


Note that the three INT() are not really required, since VBA would
automatically convert the three substrings to the intended integers, but it
does not hurt explicitly forcing the conversion ourselves.
(I assumed your string was like "dd-mm-yyyy" ).


Vanderghast, Access MVP
 
Back
Top