Dear readers
I am stuck to a logging system which stores dates in a text format like
Datestr = ‘09-MAY-16 21:41:01.3’
I have computers which are configured as Norwegian, English, French
Problem with Norwegian is for example that the month MAY is not recognized (because Norwegion is written as MAI). So in my SQL query I tried several solutions which recreates a date like for example
SELECT LEFT([Datestr],3) & MnthNr(MID([Datestr],4,3)) & MID ([Datestr],7,9) as dtetime
Where my function MnthNr just returns 5
Result is Datestr = ‘09-5-16 21:41’
But still this is not enough, because some computers still does not consider this a date format, I guess due to the – character which should be a .
I have worked with format function, but also here for example conversion Format(Date,”dd\/mm\/yy hh:mm”) can give a problem because yy is not the code for year in Norwegian. And when I use åå it does not work in other languages than Norwegian
Question: Who knows a robust method to replace a string datefield (in English) to a date format which is selected by the regional settings, preferably working in a SQL string
Many thanks in advanced
Ben
I am stuck to a logging system which stores dates in a text format like
Datestr = ‘09-MAY-16 21:41:01.3’
I have computers which are configured as Norwegian, English, French
Problem with Norwegian is for example that the month MAY is not recognized (because Norwegion is written as MAI). So in my SQL query I tried several solutions which recreates a date like for example
SELECT LEFT([Datestr],3) & MnthNr(MID([Datestr],4,3)) & MID ([Datestr],7,9) as dtetime
Where my function MnthNr just returns 5
Result is Datestr = ‘09-5-16 21:41’
But still this is not enough, because some computers still does not consider this a date format, I guess due to the – character which should be a .
I have worked with format function, but also here for example conversion Format(Date,”dd\/mm\/yy hh:mm”) can give a problem because yy is not the code for year in Norwegian. And when I use åå it does not work in other languages than Norwegian
Question: Who knows a robust method to replace a string datefield (in English) to a date format which is selected by the regional settings, preferably working in a SQL string
Many thanks in advanced
Ben