Solved

# date query

Posted on 2005-04-26
Medium Priority
308 Views
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
0
Question by:Allen Pitts
• 2

LVL 28

Expert Comment

ID: 13867980
To extract records for 2005, you can something like this:

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
0

LVL 28

Accepted Solution

rafrancisco earned 500 total points
ID: 13867992
Another way for the first query is:

SELECT * FROM IMA_Map_Information
WHERE YEAR(DT_Created) = 2005

and for the second query:

SELECT * FROM IMA_Map_Information
WHERE YEAR(DT_Created) = 2005 AND DAY(DT_Created) = 24
0

LVL 34

Expert Comment

ID: 13868210
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.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
###### Suggested Courses
Course of the Month16 days, 18 hours left to enroll

#### 862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.