?
Solved

SQl query with date

Posted on 2009-03-31
9
Medium Priority
?
580 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 2000 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

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

771 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