converting dates with different formats to numbers

[12345678]

Hi,
I have a huge database where each column consists of millions of observations. one column is a column of dates but in text format (Dates1 column) and the second column is a column of dates but in a date/time format (Dates2 column).
First I tried to have them in the same format by changing the type in design view but I was always receiving an out of memory error. I would like to obtain instead of dates numbers in the two columns. Is their a way of doing so?

 

[raskew]

Hi -

What are the precise current formats for your two columns?

When you say 'obtain numbers instead of dates', what precisely are you attempting to see? Please post examples.

Bob

 

[12345678]

the format is dd/mm/yyyy in both columns for example 18/02/1999 but the datatype for one column is text and for the other is date/time. I want to obrain a date number such as 36209 (which corresponds to 18th of February 1999)

 

[Brianwarnock]

Cdble(cdate(yourfield)) should work on the text date and just cdble on the date/time field.

Brian

 

[12345678]

I am receiving :undefined function Cdble in expression. I am using office 2007. the query that I ran was:

UPDATE 2002 SET [2002].TRD_EXCTN_DT = Cdble(CDate([2002].TRD_EXCTN_DT)), [2002].maturity = Cdble([2002].[maturity]);
where 2002 is the name of the table;TRD_EXCTN_DT is the text column and maturity is the date/time column

 

[Brianwarnock]

Sorry typo should be Cdbl no e

lookup conversion functions in help

Brian

 

[ChrisO]

Also, there should be no need to convert ‘maturity’ since it is already a double.

 

[Brianwarnock]

Had not noticed that you were doing a table update, thought that you just wanted to show results in a query.

Chris is absolutely correct in that Date/time fields are stored as Double, before the decimal giving the date and after, the time. The Format is essentially for display purposes.

I would not update the Text date in situ but create a new column so that any problems can be resolved with easy reference to the original daa.

Brian