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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 filtering datetime parameter

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 TableName
WHERE CONVERT(VARCHAR,ColumnName,120) Like '%2001-01-01 00:00:00%'

veeranjaneyulu
Go to Top of Page

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.
Go to Top of Page

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))
Go to Top of Page

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.960
3) 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 #temp

Maybe there are some other ways and if I am wrong, correct me.

Go to Top of Page

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.960
3) 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 #temp

Maybe 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 #temp
where 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
Go to Top of Page

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.
Go to Top of Page

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 answers

1 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 versions
select * from #temp
where 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 2

4 No that article is generic to both DATE and DATETIME datatype

I strongly suggest you to read the following to know how to effective filter on date/datetime columns

http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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 #temp
where 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.
Go to Top of Page

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 #temp
where 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 #temp
where 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 #temp
where 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.
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-09-22 : 23:26:51
weird, not sure why after you convert and can return data

SELECT *
FROM (VALUES(GETDATE())) AS SRC (col1)
WHERE CONVERT(VARCHAR,col1,120) Like '%2013-09-23 00:00:00%'
no row returned...
Go to Top of Page

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 data

SELECT *
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 difference


SELECT *
FROM (VALUES(GETDATE())) AS SRC (col1)
WHERE CONVERT(VARCHAR,col1,120) Like '%2013-09-23%'



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -