Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 22nd, 2005, 03:49 PM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Compare only the date portion of a datetime field

Experts,
I need to extract the date portion of a datetime column .

Sample SQL
---------
Select
"Project Sub ID",
"Staff Name",
"Status Reporting Date"


From
"Status"

Where
"Project Sub ID" = '1451291 Purge/Archival Processing' and
"Staff Name" = 'Pradhan, Ismail' and
"Status Reporting Date" = '04/15/2005'.

The "Status Reporting Date" is a datetime field , so it is stored as '04/15/2005 14:55:56".
In the above compare it does not match because it is comparing '04/15/2005 14:55:56" with '04/15/2005 00:00:00'.

Is there some built in function in Access I can apply to my SQL to get to only the date part of the "Status Reporting Date" field ?

Appreciate your help.

Thanks

 
Old April 23rd, 2005, 01:14 AM
Friend of Wrox
 
Join Date: Apr 2005
Location: Cochin, , India.
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default


Use format to remove the time part

Note:Dont forget to use the CDATE to convert back to date if you have to use logical opertors

EG: CDate(Format([Status Reporting Date],"mm/dd/yyyy")) = = '04/15/2005'.


 
Old April 23rd, 2005, 11:14 AM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Prashant,
Thank you for your help.
I will try first it thing in the morning Monday and let you know.
Thanks

 
Old April 25th, 2005, 09:56 AM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Parshant,
I tried your example and it did not work. I get a "syntax error in query expression" message.
Here is what I tried :
Select
"Project Id" as project_id,
"Project Sub Id" as project_sub_id,
"Staff Name" as staff_name,
"Status Reporting Date" as reporting_dt,
CDate( Format(["Status Reporting Date"],"mm/dd/yyyy") ) = '04/15/2005' ;

From
status ;

I am running this query against Microsoft Access 2002.
Also, I tried the quesry with "= = " also ( 2 equal signs ) , that did not work too. I thought the 2 equal signs was a Java thing.

I did a serach in Access help for CDATE and it came up completely blank.
I removed CDate and tried and got the same error

Select
"Project Id" as project_id,
"Project Sub Id" as project_sub_id,
"Staff Name" as staff_name,
"Status Reporting Date" as reporting_dt,
Format(["Status Reporting Date"],"mm/dd/yyyy") = '04/15/2005' ;

From
status ;

Can you see what I am doing wrong ?
Appreciate your help

 
Old April 25th, 2005, 11:49 AM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Parshant,
I also tried it this way
Select
"Project Id" as project_id,
"Project Sub Id" as project_sub_id,
"Staff Name" as staff_name,
"Status Reporting Date" as reporting_dt,
Format(["Status Reporting Date"],"mm/dd/yyyy") = #04/15/2005# ;

From
status;

I thought I needed the "#" for the date , but still no luck.
Thanks

 
Old April 26th, 2005, 08:12 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

The syntax you were given was for the WHERE clause, not the SELECT clause.

You need to name the field when you create a formated field in the SELECT part of your query like:

Format([Status Reporting Date],"mm/dd/yyyy") as reporting_dt

For your WHERE clause use either:

Format([Status Reporting Date],"mm/dd/yyyy") = "04/15/2005"

Or:

CDate(Format([Status Reporting Date],"mm/dd/yyyy")) = #04/15/2005#

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
 
Old April 27th, 2005, 01:06 AM
Friend of Wrox
 
Join Date: Apr 2005
Location: Cochin, , India.
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I agree with Randall, and sorry for the "= =", It was typeing mistake.





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Use DateTime Field kcsathish ASP.NET 2.0 Professional 0 July 15th, 2008 01:01 AM
Need ONLY date in datetime field in Database Lucy SQL Server 2000 10 April 30th, 2007 09:20 AM
string compare in table field alxtech SQL Server 2000 1 April 27th, 2007 12:25 AM
compare these date fields and compare and get the susanring Oracle 1 July 24th, 2006 04:58 PM
Seperating Date part from a datetime field ctranjith SQL Server 2000 2 October 25th, 2004 06:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.