Text to date

[skrbi]

Hi guys!!

I have a large table that i imported from excell and there is a date field in dd.mm.yy. format (12.06.11. for june 6th 2011)

I need a query that will convert that text field called "date" to date field.

At first i wanted to enter date manualy, but there is more than 6000 dates..

Any ideas?

 

[pr2-eugin]

You have to create a new field on the table for now call it TempDateField with Type Date/Time and then save the table. Create an UPDATE Query, something like..

UPDATE [COLOR=Blue]yourTableName[/COLOR]  SET TempDateField = DateSerial(Mid([COLOR=Blue][yourCurrentStringDateField][/COLOR], 7, 2), Mid([COLOR=Blue][yourCurrentStringDateField][/COLOR], 4, 2), Mid([COLOR=Blue][yourCurrentStringDateField][/COLOR], 1, 2))

However you can also create a small function in a Module and use the method to be called from your Query too...

Public Function changeToDate(strDate As String) As Date     [COLOR=Green]
    'Considering that the Date String you obtain is of this format 12.12.12 (dd.mm.yy)[/COLOR]         
    changeToDate = DateSerial(Mid(strDate, 7, 2), Mid(strDate, 4, 2), Mid(strDate, 1, 2)) 
End Function

So in your Query you can use it as..

     UPDATE yourTableName  SET TempDateField = changeToDate([COLOR=Blue][yourCurrentStringDateField][COLOR=Black]) [/COLOR][/COLOR]

Blue bits need to change and later you can delete the old field that is String and rename the TempDateField to your old field name..

 

[skrbi]

That was fast!!!


Thanx a lot!!!

Job done!

Can you explain what are those numbers? (What is 7,2 and 4,2 etc)

 

[pr2-eugin]

Well Mid() is one of the String functions that are available in VBA..
Mid extracts portions of string, and returns the desired part of the string.. The general syntax is..

Mid ( [I][B]string-input[/B][/I] , [I][B]start_position[/B][/I], [I][B][number_of_characters_from_the_start] [/B][/I])

As in the code, strDate is the String we pass to the Function ChangeToDate, which has the date 12.06.11 as String, using

Mid ("12.06.11", 4, 2)

Instructs it to extract the String starting at index 4 and take only 2 characters, which will give the result 06 as String.. If you leave the last Optional argument, the return value would be 06.11..
DateSerial takes in String/Numeric values in the Order Year, Month and Date. so we extract the only required information and give it to the function to convert them to Date..

Hope that makes sense..

Here is a link for more information on all available Functions

 

[skrbi]

It makes perfect sense!
You re the man!!!

Thanx again!!!!!

 

[pr2-eugin]

You are welcome.. Glad to help..