find records for today only

H

Harold Ducote

this might be simple but I can't it no where,

I have a field called datelogin the data looks like this 1/10/2004
4:50:00am

what type of criteria do I have to type in if I only want to see today
records I tried now but I will not work due to I think the seconds.



Harold Ducote
(e-mail address removed)
Frank's Casing Crew
Info Resources Coordinator
337-572-2313 phone
337-572-2462 fax
"If you really want to do something,
you'll find a way;
If you don't, you'll find an excuse."
 
M

Michel Walsh

HI,



SELECT *
FROM myTable
WHERE INT(dateTimeField) = Date()




Hoping it may help,
Vanderghast, Access MVP
 
H

Harold Ducote

here is my query

SELECT *
FROM dbo_Connections
WHERE INT(Access_Time)=Date()

then i get the following error

ODBC - CALL FAILED

[Microsoft][odbc sql server driver][sql server]Implicit conversions for data
type datetime to float is not allowed. Use the CONVERT function to run this
query. (#257)

any idea on this error.


Harold Ducote
(e-mail address removed)
Frank's Casing Crew
Info Resources Coordinator
337-572-2313 phone
337-572-2462 fax
"If you really want to do something,
you'll find a way;
If you don't, you'll find an excuse."

HI,



SELECT *
FROM myTable
WHERE INT(dateTimeField) = Date()




Hoping it may help,
Vanderghast, Access MVP
 
M

Michel Walsh

Hi,


You are using MS SQL Server (I thought you used Jet). Indeed, you can
use CONVERT, not INT, to retrieve the date part of a date_time field, and
GetDate( ), instead of Date( ). In MS SQL Server, things may look like
(depends of the way you want the date representation). I assume the query is
solved by MS SQL Server directly, then a possible formulation is:


SELECT *
FROM Connections
WHERE CONVERT( CHAR(10), Access_time, 112) = CONVERT( CHAR(10), GetDate(),
112)



Note that the style 112 is: yyyymmdd
no portion for the time part, evidently...




Hoping it may help,
Vanderghast, Access MVP



Harold Ducote said:
here is my query

SELECT *
FROM dbo_Connections
WHERE INT(Access_Time)=Date()

then i get the following error

ODBC - CALL FAILED

[Microsoft][odbc sql server driver][sql server]Implicit conversions for data
type datetime to float is not allowed. Use the CONVERT function to run this
query. (#257)

any idea on this error.


Harold Ducote
(e-mail address removed)
Frank's Casing Crew
Info Resources Coordinator
337-572-2313 phone
337-572-2462 fax
"If you really want to do something,
you'll find a way;
If you don't, you'll find an excuse."

HI,



SELECT *
FROM myTable
WHERE INT(dateTimeField) = Date()




Hoping it may help,
Vanderghast, Access MVP


Harold Ducote said:
this might be simple but I can't it no where,

I have a field called datelogin the data looks like this 1/10/2004
4:50:00am

what type of criteria do I have to type in if I only want to see today
records I tried now but I will not work due to I think the seconds.



Harold Ducote
(e-mail address removed)
Frank's Casing Crew
Info Resources Coordinator
337-572-2313 phone
337-572-2462 fax
"If you really want to do something,
you'll find a way;
If you don't, you'll find an excuse."
Click to expand...
Click to expand...
 
H

Harold Ducote

i'm using a odbc driver to connect to the sql server through microsoft
access to retreive the data and i'm getting now a undefined function
'Convert' expression when running the query through ms access.



Harold Ducote
(e-mail address removed)
Frank's Casing Crew
Info Resources Coordinator
337-572-2313 phone
337-572-2462 fax
"If you really want to do something,
you'll find a way;
If you don't, you'll find an excuse."

Hi,


You are using MS SQL Server (I thought you used Jet). Indeed, you can
use CONVERT, not INT, to retrieve the date part of a date_time field, and
GetDate( ), instead of Date( ). In MS SQL Server, things may look like
(depends of the way you want the date representation). I assume the query
is
solved by MS SQL Server directly, then a possible formulation is:


SELECT *
FROM Connections
WHERE CONVERT( CHAR(10), Access_time, 112) = CONVERT( CHAR(10), GetDate(),
112)



Note that the style 112 is: yyyymmdd
no portion for the time part, evidently...




Hoping it may help,
Vanderghast, Access MVP



Harold Ducote said:
here is my query

SELECT *
FROM dbo_Connections
WHERE INT(Access_Time)=Date()

then i get the following error

ODBC - CALL FAILED

[Microsoft][odbc sql server driver][sql server]Implicit conversions for data
type datetime to float is not allowed. Use the CONVERT function to run this
query. (#257)

any idea on this error.


Harold Ducote
(e-mail address removed)
Frank's Casing Crew
Info Resources Coordinator
337-572-2313 phone
337-572-2462 fax
"If you really want to do something,
you'll find a way;
If you don't, you'll find an excuse."

HI,



SELECT *
FROM myTable
WHERE INT(dateTimeField) = Date()




Hoping it may help,
Vanderghast, Access MVP


Harold Ducote said:
this might be simple but I can't it no where,

I have a field called datelogin the data looks like this 1/10/2004
4:50:00am

what type of criteria do I have to type in if I only want to see today
records I tried now but I will not work due to I think the seconds.



Harold Ducote
(e-mail address removed)
Frank's Casing Crew
Info Resources Coordinator
337-572-2313 phone
337-572-2462 fax
"If you really want to do something,
you'll find a way;
If you don't, you'll find an excuse."
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


Seems we have a problem if the system explicitly ask for the use of
CONVERT and the same system does not allow us to use it... :)


Let us try to be smarter than the system. Have you tried

.... WHERE Access_time>= ( Date() ) AND Access_time < ( 1+ (Date()))



or GetDate( ) if Date( ) does not work?



Vanderghast, Access MVP



Harold Ducote said:
i'm using a odbc driver to connect to the sql server through microsoft
access to retreive the data and i'm getting now a undefined function
'Convert' expression when running the query through ms access.



Harold Ducote
(e-mail address removed)
Frank's Casing Crew
Info Resources Coordinator
337-572-2313 phone
337-572-2462 fax
"If you really want to do something,
you'll find a way;
If you don't, you'll find an excuse."

Hi,


You are using MS SQL Server (I thought you used Jet). Indeed, you can
use CONVERT, not INT, to retrieve the date part of a date_time field, and
GetDate( ), instead of Date( ). In MS SQL Server, things may look like
(depends of the way you want the date representation). I assume the query
is
solved by MS SQL Server directly, then a possible formulation is:


SELECT *
FROM Connections
WHERE CONVERT( CHAR(10), Access_time, 112) = CONVERT( CHAR(10), GetDate(),
112)



Note that the style 112 is: yyyymmdd
no portion for the time part, evidently...




Hoping it may help,
Vanderghast, Access MVP



Harold Ducote said:
here is my query

SELECT *
FROM dbo_Connections
WHERE INT(Access_Time)=Date()

then i get the following error

ODBC - CALL FAILED

[Microsoft][odbc sql server driver][sql server]Implicit conversions for data
type datetime to float is not allowed. Use the CONVERT function to run this
query. (#257)

any idea on this error.


Harold Ducote
(e-mail address removed)
Frank's Casing Crew
Info Resources Coordinator
337-572-2313 phone
337-572-2462 fax
"If you really want to do something,
you'll find a way;
If you don't, you'll find an excuse."

Michel Walsh<vanderghast@VirusAreFunnierThanSpam> 01/13/2004 6:38:08 AM
Click to expand...
HI,



SELECT *
FROM myTable
WHERE INT(dateTimeField) = Date()




Hoping it may help,
Vanderghast, Access MVP


Harold Ducote said:
this might be simple but I can't it no where,

I have a field called datelogin the data looks like this 1/10/2004
4:50:00am

what type of criteria do I have to type in if I only want to see today
records I tried now but I will not work due to I think the seconds.



Harold Ducote
(e-mail address removed)
Frank's Casing Crew
Info Resources Coordinator
337-572-2313 phone
337-572-2462 fax
"If you really want to do something,
you'll find a way;
If you don't, you'll find an excuse."
Click to expand...
Click to expand...
Click to expand...
 
H

Harold Ducote

wow it work thanks here is what the final sql query looks like for everyone
following this post

SELECT *
FROM dbo_Connections
WHERE Access_time>= ( Date() ) AND Access_time < ( 1+ (Date()));





Harold Ducote
(e-mail address removed)
Frank's Casing Crew
Info Resources Coordinator
337-572-2313 phone
337-572-2462 fax
"If you really want to do something,
you'll find a way;
If you don't, you'll find an excuse."

Hi,


Seems we have a problem if the system explicitly ask for the use of
CONVERT and the same system does not allow us to use it... :)


Let us try to be smarter than the system. Have you tried

.... WHERE Access_time>= ( Date() ) AND Access_time < ( 1+ (Date()))



or GetDate( ) if Date( ) does not work?



Vanderghast, Access MVP



Harold Ducote said:
i'm using a odbc driver to connect to the sql server through microsoft
access to retreive the data and i'm getting now a undefined function
'Convert' expression when running the query through ms access.



Harold Ducote
(e-mail address removed)
Frank's Casing Crew
Info Resources Coordinator
337-572-2313 phone
337-572-2462 fax
"If you really want to do something,
you'll find a way;
If you don't, you'll find an excuse."

Hi,


You are using MS SQL Server (I thought you used Jet). Indeed, you can
use CONVERT, not INT, to retrieve the date part of a date_time field, and
GetDate( ), instead of Date( ). In MS SQL Server, things may look like
(depends of the way you want the date representation). I assume the query
is
solved by MS SQL Server directly, then a possible formulation is:


SELECT *
FROM Connections
WHERE CONVERT( CHAR(10), Access_time, 112) = CONVERT( CHAR(10), GetDate(),
112)



Note that the style 112 is: yyyymmdd
no portion for the time part, evidently...




Hoping it may help,
Vanderghast, Access MVP



Harold Ducote said:
here is my query

SELECT *
FROM dbo_Connections
WHERE INT(Access_Time)=Date()

then i get the following error

ODBC - CALL FAILED

[Microsoft][odbc sql server driver][sql server]Implicit conversions for data
type datetime to float is not allowed. Use the CONVERT function to run this
query. (#257)

any idea on this error.


Harold Ducote
(e-mail address removed)
Frank's Casing Crew
Info Resources Coordinator
337-572-2313 phone
337-572-2462 fax
"If you really want to do something,
you'll find a way;
If you don't, you'll find an excuse."

Michel Walsh<vanderghast@VirusAreFunnierThanSpam> 01/13/2004
Click to expand...
Click to expand...
6:38:08
AM
HI,



SELECT *
FROM myTable
WHERE INT(dateTimeField) = Date()




Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Field: DateLogin
Criteria: >=Date() And < DateAdd("d",1,Date())

or if you are pretty sure that you won't have a date with a time of exactly midnight

Criteria: Between Date() and DateAdd("d",1,Date())
 

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

covert number to time 8
Group by min query 2
TIME QUERY 4
How to find similar records? 8
Find and Replace only works for a few thousand records then stops 2
Access Code Required For New Validation database 4
Attn Sprinks- Not duplicate insert records 1
Printing, Imaging, Fax and All-in-One FAQ for Jan 26, 2004 2

Top