Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
allan8964
Posting Yak Master
249 Posts |
Posted - 2013-09-21 : 00:40:29
|
Hi there,We can use LIKE '% ...%' for character string search but how can we do the same thing to a datatime parameter like '2013-09-20 13:34:43.098'?Thanks in advance. |
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2013-09-21 : 02:11:53
|
SELECT * FROM TableNameWHERE CONVERT(VARCHAR,ColumnName,120) Like '%2001-01-01 00:00:00%'veeranjaneyulu |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2013-09-21 : 12:18:08
|
Very smart! Convert the column to a string from datetime type then compare it with the parameter. Thank you so much. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-21 : 14:33:19
|
Doing it that way is very unconventional. Usually people keep the data as datetime and then do comparisons on that. For example, if you want to filter on all the rows for which your datestamp column is sometime today, do it like this:WHERE DatestampColumn >= CAST(GETDATE() AS DATE) and DatestampColumn < DATEADD(dd,1,CAST(GETDATE() AS DATE)) |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2013-09-21 : 17:25:15
|
Checked MSDN and read some articles and I agree that directly comparing date or datetime is more accurate than that after convert them into a string variable. But interesting thing is that I did some tests and I found out that for this particular case James' way won't work. Here are why:1) First the DateStampColumn uses DateTime type not date type.2) Second I just want pick up the data for date like '09-21-2013'. All rows in the column show something like this: 2013-09-21 19:40:11.9603) For date or datetime comparison we can't use 'LIKE' key word, so only signs of = or >=, <=, >, < can be used. Ok, because of this none of the rows in the column matches (1) CAST(GETDATE() AS DATETIME), or (2) DATEADD(DD, 0, CAST(GetDate() as Date)).4) Because we pick up '09-21-2013' from '2013-09-21 19:40:11.960', so i think only LIKE is available. Then we need convert datetime to varchar first then use LIKE and % to get the day.Here are the codes I tried.Create Table #temp(ID int identity(1,1) not null, StartDate datetime)insert into #temp(StartDate) Values ('2013-09-21 19:40:11.960');insert into #temp(StartDate) Values ('2013-09-21 19:41:41.453');select * from #temp--where StartDate = CAST(GETDATE() AS DATETIME)-- where StartDate = DATEADD(dd, 0, CAST(GETDATE() AS DATETIME))Where CONVERT(VARCHAR, StartDate, 120) Like '2013-09-21 %'Drop table #tempMaybe there are some other ways and if I am wrong, correct me. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-21 : 21:56:12
|
quote: Originally posted by allan8964 Checked MSDN and read some articles and I agree that directly comparing date or datetime is more accurate than that after convert them into a string variable. But interesting thing is that I did some tests and I found out that for this particular case James' way won't work. Here are why:1) First the DateStampColumn uses DateTime type not date type.2) Second I just want pick up the data for date like '09-21-2013'. All rows in the column show something like this: 2013-09-21 19:40:11.9603) For date or datetime comparison we can't use 'LIKE' key word, so only signs of = or >=, <=, >, < can be used. Ok, because of this none of the rows in the column matches (1) CAST(GETDATE() AS DATETIME), or (2) DATEADD(DD, 0, CAST(GetDate() as Date)).4) Because we pick up '09-21-2013' from '2013-09-21 19:40:11.960', so i think only LIKE is available. Then we need convert datetime to varchar first then use LIKE and % to get the day.Here are the codes I tried.Create Table #temp(ID int identity(1,1) not null, StartDate datetime)insert into #temp(StartDate) Values ('2013-09-21 19:40:11.960');insert into #temp(StartDate) Values ('2013-09-21 19:41:41.453');select * from #temp--where StartDate = CAST(GETDATE() AS DATETIME)-- where StartDate = DATEADD(dd, 0, CAST(GETDATE() AS DATETIME))Where CONVERT(VARCHAR, StartDate, 120) Like '2013-09-21 %'Drop table #tempMaybe there are some other ways and if I am wrong, correct me.
You made so many changes to what I suggested, that it is doing something completely different. I have fixed those - see in red below:Create Table #temp(ID int identity(1,1) not null, StartDate datetime)insert into #temp(StartDate) Values ('2013-09-21 19:40:11.960');insert into #temp(StartDate) Values ('2013-09-21 19:41:41.453');select * from #tempwhere StartDate >= CAST(GETDATE() AS DATE) AND StartDate < DATEADD(dd, 1, CAST(GETDATE() AS DATE))--Where CONVERT(VARCHAR, StartDate, 120) Like '2013-09-21 %'Drop table #temp If you are on SQL 2005 or earlier, there is no DATE datatype. In that case, the query will need to be changed a little. If that is the case, post back.This way of comparing dates is very carefully done to be precise and efficient (i.e., to allow the use of indexes if any are present).Madhivanan has a number of blogs that describe why you need to stick with datetime data types, and use this type of comparison. See here: http://beyondrelational.com/modules/2/blogs/70/posts/10953/compare-date-as-date-not-as-varchar.aspx |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2013-09-21 : 22:36:47
|
1. I have not made any changes. In my first post I said I need to filter a parameter like '2013-09-20 13:34:43.098'.2. I am running these in SQL 2008 R2 not in SQL 2005.3. You said you 'have fixed those' But I got nothing running them. Did you run those in SQL 2005?4. The article you recommended is about DATE not DATETIME. So I believe in this case your codes WON'T work.5. Prove I am wrong.Thanks. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-09-22 : 09:35:04
|
quote: Originally posted by allan8964 1. I have not made any changes. In my first post I said I need to filter a parameter like '2013-09-20 13:34:43.098'.2. I am running these in SQL 2008 R2 not in SQL 2005.3. You said you 'have fixed those' But I got nothing running them. Did you run those in SQL 2005?4. The article you recommended is about DATE not DATETIME. So I believe in this case your codes WON'T work.5. Prove I am wrong.Thanks.
My answers1 Using LIKE on Datetime column does not make any sense until you want to compare only part of datetime values (only dates by skipping time part)2 The version does not matter. The following will work for all versionsselect * from #tempwhere StartDate >= dateadd(day,datediff(day,0,getdate()),0) AND StartDate <dateadd(day,datediff(day,0,getdate()),1) 3 Try the code I suggested at point 24 No that article is generic to both DATE and DATETIME datatypeI strongly suggest you to read the following to know how to effective filter on date/datetime columnshttp://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspxMadhivananFailing to plan is Planning to fail |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2013-09-22 : 12:52:46
|
Thanks for the reply.Your point 1 already says that for my case here LIKE is the only choice. 'until you want to compare only part of datetime values (only dates by skipping time part)' as quoted, is exactly what I need.In my #temp table your codes select * from #tempwhere StartDate >= dateadd(day,datediff(day,0,getdate()),0) AND StartDate <dateadd(day,datediff(day,0,getdate()),1)NOT working, either.I think the point here is not the best way to compare the datetime but how to skip the time part to filter date part only.Article is good, thank you again. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-22 : 13:13:55
|
quote: Originally posted by allan8964 Thanks for the reply.Your point 1 already says that for my case here LIKE is the only choice. 'until you want to compare only part of datetime values (only dates by skipping time part)' as quoted, is exactly what I need.In my #temp table your codes select * from #tempwhere StartDate >= dateadd(day,datediff(day,0,getdate()),0) AND StartDate <dateadd(day,datediff(day,0,getdate()),1)NOT working, either.I think the point here is not the best way to compare the datetime but how to skip the time part to filter date part only.Article is good, thank you again.
nope the way its written it will take all records that got created on current day ie from 12 midnight until 12 midnight next day provided Startdate is of date datatype.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-22 : 22:23:39
|
quote: Originally posted by allan8964 Thanks for the reply.Your point 1 already says that for my case here LIKE is the only choice. 'until you want to compare only part of datetime values (only dates by skipping time part)' as quoted, is exactly what I need.In my #temp table your codes select * from #tempwhere StartDate >= dateadd(day,datediff(day,0,getdate()),0) AND StartDate <dateadd(day,datediff(day,0,getdate()),1)NOT working, either.I think the point here is not the best way to compare the datetime but how to skip the time part to filter date part only.Article is good, thank you again.
Can you post the exact code that you are using that is not working?If you copy the code shown below and run it from an SSMS window, you will see that it returns two rows, as one would expect. Create Table #temp(ID int identity(1,1) not null, StartDate datetime)insert into #temp(StartDate) Values ('2013-09-20 19:40:11.960');insert into #temp(StartDate) Values ('2013-09-21 19:41:41.453');insert into #temp(StartDate) Values ('2013-09-21 19:42:41.453');insert into #temp(StartDate) Values ('2013-09-22 19:43:41.453');DECLARE @date DATETIME = '20130921';select * from #tempwhere StartDate >= CAST(@date AS DATE) AND StartDate < DATEADD(dd, 1, CAST(@date AS DATE))--Where CONVERT(VARCHAR, StartDate, 120) Like '2013-09-21 %'Drop table #temp In the code above, some of the casting to DATE that I am doing is unnecessary. I kept those in so it is as close to the original code I posted. |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2013-09-22 : 23:26:51
|
weird, not sure why after you convert and can return dataSELECT *FROM (VALUES(GETDATE())) AS SRC (col1)WHERE CONVERT(VARCHAR,col1,120) Like '%2013-09-23 00:00:00%'no row returned... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-23 : 11:25:43
|
quote: Originally posted by waterduck weird, not sure why after you convert and can return dataSELECT *FROM (VALUES(GETDATE())) AS SRC (col1)WHERE CONVERT(VARCHAR,col1,120) Like '%2013-09-23 00:00:00%'no row returned...
because GETDATE has time part also. you're having time as 00:00:00 in your LIKE pattern.try this instead and see differenceSELECT *FROM (VALUES(GETDATE())) AS SRC (col1)WHERE CONVERT(VARCHAR,col1,120) Like '%2013-09-23%' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|