DistinctRow for Date portion of a DateTime column

[Guest]

I have a table with a DateTime column and both the date and time portions of
the field are used in the entries. But I want to write a query that'll do the
following:

Get a list of each distinct DATE from all of the rows of the DateTime
column. So, for example, let's say the column is named "PurchaseDate", and
some of the values look like:

PurchaseDate
1/12/2006 3:25pm
1/12/2006 6:45pm
3/3/2006 12:05am
3/3/2006 1:22am

I'd like the result of this query (for this example) to be a list as follows:

DistinctDates
1/12/2006
3/3/2006

Alex

 

[Steve Schapel]

Alex,

Try it like this...
SELECT DISTINCT DatePart([PurchaseDate]) FROM YourTable

 

[Guest]

Steve -

This is very promising but Access complains that DatePart needs a
FormatString parameter first but I can't figure out how to extract the Date.
What should that first parameter be?

Also, can you tell me how I can order the results by that same date?

Thanks so much!

Alex

Alex,

Try it like this...
SELECT DISTINCT DatePart([PurchaseDate]) FROM YourTable

--
Steve Schapel, Microsoft Access MVP

I have a table with a DateTime column and both the date and time portions of
the field are used in the entries. But I want to write a query that'll do the
following:

Get a list of each distinct DATE from all of the rows of the DateTime
column. So, for example, let's say the column is named "PurchaseDate", and
some of the values look like:

PurchaseDate
1/12/2006 3:25pm
1/12/2006 6:45pm
3/3/2006 12:05am
3/3/2006 1:22am

I'd like the result of this query (for this example) to be a list as follows:

DistinctDates
1/12/2006
3/3/2006

Alex

 

[Steve Schapel]

Doh! I am very sorry, it was too early in the morning for me! I meant
to say DateValue, not DatePart.
SELECT DISTINCT DateValue([PurchaseDate]) FROM YourTable

And you should just be able to sort on this field in your query...
SELECT DISTINCT DateValue([PurchaseDate])
FROM YourTable
ORDER BY DateValue([PurchaseDate])

 

[Guest]

Steve -

Tahnks so much. Unfortunately, I'm still having a wee problem: My SQL query
(done in Access for now) currently looks like:

SELECT DISTINCT DateValue(StartPurch)
FROM PurchQAT
ORDER BY DateValue(StartPurch)

Now, "StartPurch" is a column in table "PurchQAT" of plain old type DateTime
(again, in Access). When I try to execute this query, I get "Data type
mismatch in creteria expression."

Any thoughts why this might be?I can't figure it out!

Thanks again!

Ax

Doh! I am very sorry, it was too early in the morning for me! I meant
to say DateValue, not DatePart.
SELECT DISTINCT DateValue([PurchaseDate]) FROM YourTable

And you should just be able to sort on this field in your query...
SELECT DISTINCT DateValue([PurchaseDate])
FROM YourTable
ORDER BY DateValue([PurchaseDate])

--
Steve Schapel, Microsoft Access MVP

Steve -

This is very promising but Access complains that DatePart needs a
FormatString parameter first but I can't figure out how to extract the Date.
What should that first parameter be?

Also, can you tell me how I can order the results by that same date?

 

[Guest]

WAIT - So I've now figured out that the error is becuase of the use of
DISTINCT. Hmmm... So why's that???

Alex

Steve -

Tahnks so much. Unfortunately, I'm still having a wee problem: My SQL query
(done in Access for now) currently looks like:

SELECT DISTINCT DateValue(StartPurch)
FROM PurchQAT
ORDER BY DateValue(StartPurch)

Now, "StartPurch" is a column in table "PurchQAT" of plain old type DateTime
(again, in Access). When I try to execute this query, I get "Data type
mismatch in creteria expression."

Any thoughts why this might be?I can't figure it out!

Thanks again!

Ax

Doh! I am very sorry, it was too early in the morning for me! I meant
to say DateValue, not DatePart.
SELECT DISTINCT DateValue([PurchaseDate]) FROM YourTable

And you should just be able to sort on this field in your query...
SELECT DISTINCT DateValue([PurchaseDate])
FROM YourTable
ORDER BY DateValue([PurchaseDate])

--
Steve Schapel, Microsoft Access MVP

Steve -

This is very promising but Access complains that DatePart needs a
FormatString parameter first but I can't figure out how to extract the Date.
What should that first parameter be?

Also, can you tell me how I can order the results by that same date?

 

[Guest]

OKAY, my last note on this - Turns out that the error comes around in two
places in the query you sent me:
1. When I use "DISTINCT" and
2. When I use DateValue() in the ORDER BY clause.

Hmmmmmmmmm...

Alex

Steve -

Tahnks so much. Unfortunately, I'm still having a wee problem: My SQL query
(done in Access for now) currently looks like:

SELECT DISTINCT DateValue(StartPurch)
FROM PurchQAT
ORDER BY DateValue(StartPurch)

Now, "StartPurch" is a column in table "PurchQAT" of plain old type DateTime
(again, in Access). When I try to execute this query, I get "Data type
mismatch in creteria expression."

Any thoughts why this might be?I can't figure it out!

Thanks again!

Ax

Doh! I am very sorry, it was too early in the morning for me! I meant
to say DateValue, not DatePart.
SELECT DISTINCT DateValue([PurchaseDate]) FROM YourTable

And you should just be able to sort on this field in your query...
SELECT DISTINCT DateValue([PurchaseDate])
FROM YourTable
ORDER BY DateValue([PurchaseDate])

--
Steve Schapel, Microsoft Access MVP

Steve -

This is very promising but Access complains that DatePart needs a
FormatString parameter first but I can't figure out how to extract the Date.
What should that first parameter be?

Also, can you tell me how I can order the results by that same date?

 

[Steve Schapel]

Alex,

It should be ok. The only thing I can think of is if you have some
records with nothing in the StartPurch field. If that is the case, try
it like this...
SELECT DISTINCT DateValue([StartPurch])
FROM PurchQAT
WHERE [StartPurch] Is Not Null
ORDER BY DateValue([StartPurch])

 

[Wei Lu [MSFT]]

Hi Ax,

Thank you for the posting.

It's weird. The query should work, and I tested on my side, it works fine.

Would you please let me know the Access Version you use and the detail
column information?

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Steve Schapel]

Wei,

Did you test it with nulls in the field?

 

[Wei Lu [MSFT]]

Hi Steve,

Yes, as you describe, if the database file contains the NULL value, we will
meet the error.

I agree with you that the following query is suitable for the NULL value
case:

SELECT DISTINCT DateValue([StartPurch])
FROM PurchQAT
WHERE [StartPurch] Is Not Null
ORDER BY DateValue([StartPurch])

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

You ROCK! Tahnks so much.

Alex

Alex,

It should be ok. The only thing I can think of is if you have some
records with nothing in the StartPurch field. If that is the case, try
it like this...
SELECT DISTINCT DateValue([StartPurch])
FROM PurchQAT
WHERE [StartPurch] Is Not Null
ORDER BY DateValue([StartPurch])

--
Steve Schapel, Microsoft Access MVP

OKAY, my last note on this - Turns out that the error comes around in two
places in the query you sent me:
1. When I use "DISTINCT" and
2. When I use DateValue() in the ORDER BY clause.