Solved

SQl query with date

Posted on 2009-03-31
9
577 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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
Convert VBA UDF to SQl SERVER UDF 4 50
Linked Server - SP with Param to VIew 7 22
SQL- GROUP BY 4 24
UPDATE JOIN multiple tables 5 21
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

749 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