SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 filtering datetime parameter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

allan8964
Posting Yak Master

247 Posts

Posted - 09/21/2013 :  00:40:29  Show Profile  Reply with Quote
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

India
165 Posts

Posted - 09/21/2013 :  02:11:53  Show Profile  Reply with Quote
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

247 Posts

Posted - 09/21/2013 :  12:18:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 09/21/2013 :  14:33:19  Show Profile  Reply with Quote
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

247 Posts

Posted - 09/21/2013 :  17:25:15  Show Profile  Reply with Quote
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.


Edited by - allan8964 on 09/21/2013 17:44:05
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 09/21/2013 :  21:56:12  Show Profile  Reply with Quote
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

247 Posts

Posted - 09/21/2013 :  22:36:47  Show Profile  Reply with Quote
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.

Edited by - allan8964 on 09/21/2013 22:39:59
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 09/22/2013 :  09:35:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

247 Posts

Posted - 09/22/2013 :  12:52:46  Show Profile  Reply with Quote
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.

Edited by - allan8964 on 09/22/2013 12:57:02
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/22/2013 :  13:13:55  Show Profile  Reply with Quote
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

Edited by - visakh16 on 09/22/2013 13:15:13
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 09/22/2013 :  22:23:39  Show Profile  Reply with Quote
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

Malaysia
961 Posts

Posted - 09/22/2013 :  23:26:51  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 09/23/2013 :  11:25:43  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000