converting text dates into date format

[PANKAJINFO1]

Hi all,

I am new to this forum and have a sound knowledge of Access.

Please solve



in a data base i have date in text format 04.01.2003 (dd.mm.yyyy).


I want to store/update all the text date to be converted into date format so that i can use select command through dates between.


Regards & Thanks
Pankaj

 

[namliam]

Have a look at the Left, Right and Mid functions to pick appart your string into Day, Month and Year.

Then use the Dateserial function to turn them into a date.

Welcome to AWF

 

[PANKAJINFO1]

Please provide some example .

Thanks

 

[WayneRyan]

Pankaj,

For U.S. dates:

CDate(Replace(YourField, ".", "/"))

Wayne

 

[raskew]

Hi -

i have date in text format 04.01.2003 (dd.mm.yyyy).

To convert your example to US format (mm/dd/yyyy):

x = "04.01.2003"
? dateserial(right(x,4), mid(x,4,2), left(x,2))
1/4/2003

Bob

 

[WayneRyan]

Thanks Bob,

I probably won't answer any more questions at 4:00 AM.

Wayne

 

[gemma-the-husky]

panka

i thought you had a sound knowledge of access

your query implies you dont really

 

[namliam]

Pankaj,

For U.S. dates:

CDate(Replace(YourField, ".", "/"))

Wayne

NOT the Cdate !!!

Dateserial PLEASE! You know better than to advertise the use of CDate Wayne... I am sure of it !

@Dave, yes your Male !! LOL
I guess we have our own definitions of "sound", I know some collegues who call themselves "experts", yet they come to me asking questions....
Yet I wouldnt dare to call my self an expert on anything...

 

[neileg]

Erm, pardon my ignorance but what's the issue with CDate? It's never caused me a problem.

 

[namliam]

Well if you know EXACTLY what you are doing and/or always work with US dates there is not a problem.

However many a visitor on AWF is not US based and thus will have Euro dates not US dates. Using CDate will convert 06-07-2009 to 7 June, not 6 July in most cases.

Dateserial has pre-fixed points of entry => Dateserial(Year, Month, Day)
This will not be leading to any problems ever... Dateserial, due to its clarity and 100% solution rate is by far preverable to CDate or anyother date conversion....
Perhaps something like CDate( Month & "/" & Day & "/" & Year) (or offcourse ISO format) will rival it... but by default I tend to go for clarity and that is DateSerial!

 

[PANKAJINFO1]

I want to change Text datatype in Date datatype 04.01.2003 (dd.mm.yyyy).


Please help

 

[namliam]

Did you read all responses???

See below a quote from earlier by Bob, just about GIVING you your solution!

You really should "sound out" your knowledge of Left, Right and Mid functions though

Hi -

To convert your example to US format (mm/dd/yyyy):

x = "04.01.2003"
? dateserial(right(x,4), mid(x,4,2), left(x,2))
1/4/2003

Bob

 

[neileg]

namliam, thanks very much. Nice clear and understandable explanation.

I wish some of my native english speaking colleagues could be as good at explaining things!

 

[namliam]

namliam, thanks very much. Nice clear and understandable explanation.

I wish some of my native english speaking colleagues could be as good at explaining things!

Great ! Thanks for the complement... But I am sure there is lots of comments to be made about that particular post... To most my direct colleagues, most of which are native UK / US people, I might as well be speaking French... As many such an explenation has been done 10 times over and they still dont seem to get it...

Then again... It keeps me in a job