DistinctRow for Date portion of a DateTime column


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
 
Ad

Advertisements

S

Steve Schapel

Alex,

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

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


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
 
S

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])
 
G

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


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?
 
G

Guest

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?
 
Ad

Advertisements

G

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



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?
 
S

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])
 
W

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.
 
W

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.
 
Ad

Advertisements

G

Guest

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.
 

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

Top