If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Convert" command?
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. |
#2
|
|||
|
|||
"Convert" command?
hi Ricardo,
Ricardo Vazquez wrote: Is there a SQL command for MS Access similar to the CONVERT command in Transact-SQL? Not SQL, but VBA. Take a look at CDate(), CStr(), CLng, etc in the OH. mfG -- stefan -- |
#3
|
|||
|
|||
"Convert" command?
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 "Ricardo Vazquez" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|