?
Solved

date query

Posted on 2005-04-26
3
Medium Priority
?
308 Views
Last Modified: 2010-03-19
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
Comment
Question by:Allen Pitts
  • 2
3 Comments
 
LVL 28

Expert Comment

by:rafrancisco
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

by:
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

by:Brian Crowe
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

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.

Join & Ask a Question