Allen Pitts
asked on
date query
Good morning expert,
Have learned a lot about doing complex queries and subqueries
but having some difficulty doing simple queries on columns with
datatime data type. For instance
SELECT *
FROM IMA_Map_Information
WHERE (dt_created LIKE '%2005%')
works (after complaining "entry cannot being
converted to a valid datetime value")
but
SELECT *
FROM IMA_Map_Information
WHERE (dt_created LIKE '%24/2005%') works
returns nothing although I know there is
a date like 04/24/2005 exists in the column.
Am i making a syntactical error?
Have searched in several books and on
the 'net for info on queries on datetime
columns with no success. Is there
any knowledge base on these queiries?
Thanks.
Allen in Dallas
Have learned a lot about doing complex queries and subqueries
but having some difficulty doing simple queries on columns with
datatime data type. For instance
SELECT *
FROM IMA_Map_Information
WHERE (dt_created LIKE '%2005%')
works (after complaining "entry cannot being
converted to a valid datetime value")
but
SELECT *
FROM IMA_Map_Information
WHERE (dt_created LIKE '%24/2005%') works
returns nothing although I know there is
a date like 04/24/2005 exists in the column.
Am i making a syntactical error?
Have searched in several books and on
the 'net for info on queries on datetime
columns with no success. Is there
any knowledge base on these queiries?
Thanks.
Allen in Dallas
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One thing to keep in mind is that the datetime is stored as 2 4-byte integers. One for days since 1/1/1900 and one for milliseconds (actually 3/1000's of a second) since midnight. So, the "05/25/2005 1:13:25 PM" representation that you see is a format applied to the value. You would have to convert the date to a string value to do the comparison you give
SELECT *
FROM IMA_Map_Information
WHERE CONVERT(varchar, dt_created, 101) LIKE '%2005%'
but obviously using the DATEPART function as francisco suggests is the better solution.
SELECT *
FROM IMA_Map_Information
WHERE CONVERT(varchar, dt_created, 101) LIKE '%2005%'
but obviously using the DATEPART function as francisco suggests is the better solution.
SELECT * FROM IMA_Map_Information
WHERE DATEPART(YEAR, DT_Created) = 2005
For a given day and year, you can do this:
SELECT * FROM IMA_Map_Information
WHERE DATEPART(YEAR, DT_Created) = 2005 AND
DATEPART(DD, DT_Created) = 24