Date as a number

[Sarma]

I have a query the result of which provides me a date field in the format 12-Oct-2010.

I want that this field data to be converted to corresponding numbers such as 40463.

I seek your help.

Regards.

Sarma

 

[Galaxiom]

Assuming your field is a datetime datatype rather than a string:

CLng([datefield])

But I am curious as to why do you require this.

 

[Sarma]

Thanks, Galaxiom!

I have dates & invoices. I needed cumulative invoice value against each date. However, when I made the usual command, I am not getting the answer. What I did now is, I converted date into a number and then built the query for cumulative invoices. It worked very well. Grateful.

 

[Galaxiom]

It should still have worked with the date as a date. Indeed dates are stored as numbers and just displayed in the designated date format.

Conversion to Long does drop the time component of a datetime. I suspect you have a time component in your date field and that is the real problem

 

[Brianwarnock]

In which case Datevalue(datefield) would achieve the result and been clearer .

Brian

 

[Sarma]

Galaxiom / Brainwarnock: Even date field would have achieved the same result. There was a small mistake I did. In the query design mode, I should have put Group By - Expression which works. Thanks.