parsing date

[zhuanyi]

hello,
I have a module that I would like to parse the original date of birth in the format of dd/mm/yyyy (i.e., 01/05/2007) to the format of yyyymmdd(i.e., 20070501) using SQL statement copy them into another table at the same time, may I know how could this be accomplished programmatically? Thanks!

 

[modest]

Format([Date Field],"yyyymmdd")

So your SQL would be similar to:
sSQL = "SELECT Format([Date Field],""yyyymmdd"") INTO [New Table Name] FROM [Table From]"

Where "Date Field" is the name of the field that's a date, "New Table Name" will be the name of the table created, and "Table From" is the table the Date Field is located in (where the data is stored currently).

 

[zhuanyi]

Hello,
I tried to put in the line as follows:
qryString = qryString & "SET [DOB]= FORMAT(" & srcTabName & ".[DOB1],'yyyymmdd'),"
where qryString is my SQL string, srcTabName is my source table name and I am planning to parse from DOB1 of sourceTable to DOB of target table.
When I tried to run this line, I got an syntax error...

may i know where could be the problem?

thanks!

 

[zhuanyi]

problem resolved, thanks a lot!

 

[modest]

You might want to post your final solution for other users.

 

[zhuanyi]

To be honest, I have no idea how did I fix it, I think somehow my source tables were screwed up and that is why i had lots of garbage data in the table, it worked after I cleaned up the data, thanks a lot!