find records for today only

  • Thread starter Thread starter Harold Ducote
  • Start date Start date
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."
 
HI,



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




Hoping it may help,
Vanderghast, Access MVP
 
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
 
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...
 
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...
 
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...
 
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...
 
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())
 
Back
Top