Solved

SQl query with date

Posted on 2009-03-31
9
578 Views
Last Modified: 2012-05-06
I use the following syntax to filter by date. It works great when I use the '<' or '>' opertaors on the dattime datatype column. What i want is to get all the records with a specific day(date) say 03/10/09. when i add a '=' operator i get no records.

 where last_update_date < convert(datetime, '2009-01-23', 120)

but when i add this

last_update_date = convert(datetime, '2009-01-23', 120)

i get 0 results.

can anyone rectify this or give me an alternative...
0
Comment
Question by:isaackhazi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 24036531
Hi isaackhazi,
Try DATEDIFF() function.
http://msdn.microsoft.com/en-us/library/ms189794.aspx
eg:
...
where DATEDIFF(day, last_update_date, '2009-01-23 00:00:00.0000000')=0

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 24036535
try this

Convert(datetime, last_update_date, 120)= convert(datetime, '2009-01-23', 120)
0
 
LVL 7

Expert Comment

by:luani
ID: 24036566
when you use convert(datetime, '2009-01-23', 120) it means '2009-01-23 00.00.000'
so every other date like '2009-01-23 09.11.000' so including the exact time will be different, so you got no results on "="
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 8

Author Comment

by:isaackhazi
ID: 24036602
so what do i have to use instead of 120??
0
 
LVL 7

Expert Comment

by:luani
ID: 24036634
use 101 ?
0
 
LVL 7

Expert Comment

by:luani
ID: 24036636
or as suggested above Datediff is a valid solution.........
0
 
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24036644
use CONVERT(varchar(8), last_update_date, 112)  = CONVERT(varchar(8), '2009-01-23', 112)  
0
 
LVL 8

Expert Comment

by:lexiflex
ID: 24037113
Although udayakumarlm's solution works this means any available indexes will not be used. This solution makes allowes for indexes to be used.
WHERE last_update_date >= CONVERT(DATETIME, '2009-01-23', 120)
AND last_update_date < DATEADD(DAY, 1, CONVERT(DATETIME, '2009-01-23', 120))

Open in new window

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 24042347
datediff can work, and converting last_update_date can work, but will result in table scans because you are changing the data basis / effectively creating a runtime computed field. Not always bad, but generally on large tables / results sets can cause a couple of problems...

The challenge you have is removing or accommodating the time component. style code 120 also contains a time component, so possible not the best choice of style code unless you limit by length - and char is a better choice than varchar.

Better still is to make sure you always match the datatype of the underlying table source data.

assuming '2009-01-23' is a parameter of some description, then need to use style code 120 to match the layout of that string :

WHERE last_update_date between CONVERT(DATETIME, '2009-01-23', 120)  and CONVERT(DATETIME, '2009-01-23'  + ' 23:59:59.997', 120)

Or, if part of a procedure, then fix up that string first :

declare @sd datetime
declare @ed datetime
set @sd = CONVERT(DATETIME, '2009-01-23', 120)
set @ed = CONVERT(DATETIME, '2009-01-23'  + ' 23:59:59.997', 120)

then your where clause is ALL datebased and will work much better :

WHERE last_update_date between @SD and @ED


BUT, if you care to elaborate a bit more on how that '2009-01-23'  is actually being generated, then might be able to give you a few more pointers. For example, did you know that yyyymmdd and yyyy-mm-dd are both implictly understood in a convert statement and no real need for any style code ?

select convert(datetime, '2009-03-23' + ' 23:59:59.997', 120), convert(datetime, '2009-03-23' + ' 23:59:59.997'), convert(datetime, '20090323' + ' 23:59:59.997')

Also in SQL2008 you have the DATE construct which ignores the time component - and a whole lot more - and might change the approach ?

SELECT  CAST('2009-01-23 23:59:59. 1234567' AS time(7)) AS 'time'
    ,   CAST('2009-01-23 23:59:59. 1234567' AS date) AS 'date'
    ,   CAST('2009-01-23 23:59:59.123' AS smalldatetime) AS 'smalldatetime'
    ,   CAST('2009-01-23 23:59:59.123' AS datetime) AS 'datetime'
    ,   CAST('2009-01-23 23:59:59. 1234567 +12:15' AS datetime2(7)) AS 'datetime2'
    ,   CAST('2009-01-23 23:59:59.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset'




0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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