kenuk110
asked on
Unable to retrieve CURDATE() from table.
Hi,
I have a table which contains a datetime column. When I run a query to bring back only records with todays date it retrieves nothing.
My table is called: smsin - and the field is called: validDate
This field is in datetime format in the database.
The SQL code I'm running is : SELECT * FROM smsin WHERE validDate = CURDATE()
I have a table which contains a datetime column. When I run a query to bring back only records with todays date it retrieves nothing.
My table is called: smsin - and the field is called: validDate
This field is in datetime format in the database.
The SQL code I'm running is : SELECT * FROM smsin WHERE validDate = CURDATE()
Can you also try following and see if you get the desired results:
SELECT * FROM smsin WHERE validDate = sysdate
SELECT * FROM smsin WHERE validDate = sysdate
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
barfi,
the sysdate() function returns a full timestamp, including time. Only date is wanted in this case.
the sysdate() function returns a full timestamp, including time. Only date is wanted in this case.
ASKER
Hi,
All I get is syntax error when I try to run the scripts provided? I'm using MySQL 5.1 and using Navicat to run the script from the console, not sure if this makes a difference?
All I get is syntax error when I try to run the scripts provided? I'm using MySQL 5.1 and using Navicat to run the script from the console, not sure if this makes a difference?
ASKER
Hi,
The first solution you provided works a treat.
Thanks.
Ken
The first solution you provided works a treat.
Thanks.
Ken
You can use the DAY,MONTH and YEAR Functions
or using sql functions like verifing the current day in which day of the year is and validating to the one you have in the database.
here are some more date and time functions for sql
http://msdn.microsoft.com/en-us/library/ms186724.aspx#GetDateandTimeParts
hope this helps, regards!
or using sql functions like verifing the current day in which day of the year is and validating to the one you have in the database.
here are some more date and time functions for sql
http://msdn.microsoft.com/en-us/library/ms186724.aspx#GetDateandTimeParts
hope this helps, regards!
select * from test where DAY(validDate)=DAY(GETDATE()) AND MONTH(validDate)=MONTH(GETDATE()) and YEAR(validDate)=YEAR(GETDATE())
or try this
select * from test where DATEPART(dayofyear,validDate)=DATEPART(dayofyear,GETDATE())
Please show the scripts/statements you are trying to run and the corresponding error message. It's hard for us to guess what is going wrong...
Try this in MySQL
select * from issues where DAYOFYEAR(hrdate)=DAYOFYEA R(now())
there not the same functions in sql or mysql
select * from issues where DAYOFYEAR(hrdate)=DAYOFYEA
there not the same functions in sql or mysql
Here are some more info on Date and Time functions in MySQL
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
Open in new window