Format string date to normal date (1 Viewer)

boerbende

Ben
Local time
Today, 19:22
Joined
Feb 10, 2013
Messages
339
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
 

stopher

AWF VIP
Local time
Today, 18:22
Joined
Feb 1, 2006
Messages
2,396
The DateSerial function should help you.
 

boerbende

Ben
Local time
Today, 19:22
Joined
Feb 10, 2013
Messages
339
hello stopher. Thank you. But what about the time then?
 

stopher

AWF VIP
Local time
Today, 18:22
Joined
Feb 1, 2006
Messages
2,396
Use TimeValue to covert a time string to a date/time

You can add (+) the result of the two functions together or use DateAdd
 

boerbende

Ben
Local time
Today, 19:22
Joined
Feb 10, 2013
Messages
339
Thank you Stopher.
Difficult to admit I had used dateserial before and forgot to try this here :-(

My solution is now:
SELECT DateSerial(2000+Mid([datestr],8,2),mnthnr(Mid([datestr],4,3)),Left([datestr],2)) & ' ' & Mid([datestr],11,5)

I did not use the + option with timeserial.
I was blocked by a failuremessage), but to ADD the string at the end worked OK in my dateaxes in my graph
 

Users who are viewing this thread

Top Bottom