DistinctRow for Date portion of a DateTime column

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Alex,

Try it like this...
SELECT DISTINCT DatePart([PurchaseDate]) FROM YourTable
 
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


Steve Schapel said:
Alex,

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

--
Steve Schapel, Microsoft Access MVP

Alex said:
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
Click to expand...
Click to expand...
 
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 -

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


Steve Schapel said:
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


Alex said:
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?
Click to expand...
Click to expand...
 
WAIT - So I've now figured out that the error is becuase of the use of
DISTINCT. Hmmm... So why's that???

Alex



Alex Maghen said:
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


Steve Schapel said:
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


Alex said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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



Alex Maghen said:
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


Steve Schapel said:
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


Alex said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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])
 
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.
 
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.
 
You ROCK! Tahnks so much.

Alex

Steve Schapel said:
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

Alex said:
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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Help - Distinct Row with Date Portion of a DateTime 2
Can't query datetime field formatted as m/d/yyyy 1
Count issue continued from "text then date ascending count" 8
Using results from a Sub Query in a Crosstab Query 4
Top 3 dates in calculation Function / Query? 3
numbering records according to date and ID 2
date parameter not accurate... 3
Count up by date for each customerID 2

Back
Top