How can I take the date from a date/time field? 2

[sagamw]

Hi guys!

I have a query, and a field (from the table I get the data) is a date/time field like: 5/12/2009 15:50

I want to create another field in my query that has only the date part. I struggled with some date functions but I didn't succeed.

I want to create a combo box that shows only the unique dates:
for example if the dates/times in my table are:
- 4/12/2009 11:38
- 4/12/2009 11:45
- 5/12/2009 15:30
- 5/12/2009 15:50
I want my combo box to show only:
- 4/12/2009
- 5/12/2009

Any help will be appreciated!

 

[PHV]

You may try something like this (SQL code):
SELECT DISTINCT Int([DateTimeField]) FROM yourTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sagamw]

Thank you PHV, I will try it in my query asap.
(I'm not in my db pc right now)

Btw is there a function for this (to abstract the date from a date/time field)? I found only the datepart function, but as far as I know you only get one part (like the month).

 

[MajP]

Yes like PHV showed it is "Int". All vb dates are stored as an integer part and a decimal part. What you see is a format applied to this number. The integer part represents the number of days since 12/30/1899, and the decimal represents fractions of a day.
Right now 12/8/2009 11:27:59 PM is stored as 40155.9777430556

40155 whole days since 12/30/1899
and .9777430556 of another day

so the int(40155.9777430556) returns 40155 which is 12/08/2009
Which can be proved by
format(40155,"mm/dd/yyyy") = 12/08/2009

 

[sagamw]

It worked!
Thank you PHV (and MajP for the detailed explanation).
(I gave 1 star each for your contribution!)

It worked!