| Author |
Topic |
|
rnelsch
Starting Member
31 Posts |
 Posted - 2012-06-05 : 11:36:24
|
| I have a date/time field that I want to run a "Where" clause on but I need it to ignore the time. I tried the following to convert the date:DATEADD(dd, 0, DATEDIFF(dd, 0, `call`.StartTime)) But my date select are just for mm/dd/yyyy and I think the 'time' stamps are interfering so what can I add in the query to get it to ignore the time and read the Where clause on just the date?Ryan A Nelsch |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-05 : 11:39:42
|
| I think it's the thing you are comparing with that might be the problem.You need to convert both to datetime.where DATEADD(dd, 0, DATEDIFF(dd, 0, [call].StartTime)) = convert(datetime,@mydate,101) ==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rnelsch
Starting Member
31 Posts |
Posted - 2012-06-05 : 11:46:44
|
| Is the @mydate a variable? I've never used that token before? Ryan A Nelsch |
|
|
|
rnelsch
Starting Member
31 Posts |
Posted - 2012-06-05 : 11:48:15
|
| More importantly, why cant beer be cold and fizzy? lolRyan A Nelsch |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-06-05 : 12:03:19
|
| What are your data types? Is there and error or are you not getting the resutls you want? What version of SQL Server are you using?Please supply DDL, DML and expected output. Here some links that can help you with that:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-05 : 12:05:59
|
| What are yoou comparing with then - where do you get yoour value mm/dd/yyyy from?Something can be cold and fizzy - and they do mistakenly call it beer in some countries (and their sql suffers for it)==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rnelsch
Starting Member
31 Posts |
Posted - 2012-06-05 : 12:06:28
|
| Does this help:Select `call`.CallID, `call`.StartTime, `call`.Extension, `call`.DialedNumber, `call`.CallerID, `call`.CallTypeFrom `call`Where `call`.StartTime between '@Request.dteSelectBeginningDate~' and '@Request.dteSelectEndingDate~' and CallType = '2' and DATEADD(dd, 0, DATEDIFF(dd, 0, [call].StartTime)) = convert(datetime,@mydate,101)The @request... are tokens that I use through my reporting tool to enter the dates from an Input Date element. Works just fine when there is just a date, but not when there is a date and time?Ryan A Nelsch |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-06-05 : 12:09:13
|
quote:
Originally posted by rnelsch
Does this help:<snip>Ryan A Nelsch
No, see my post from 06/05/2012 : 12:03:19 |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-05 : 12:12:01
|
| what is '@Request.dteSelectBeginningDate~'That shoould just be a string and you should get an error if `call`.StartTime is a date.Is this sql server?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rnelsch
Starting Member
31 Posts |
Posted - 2012-06-05 : 13:25:24
|
| the @request is a data token I use through the reporting tool that creates the reports for me. Its an xml tool that renders a web report for me. The tokens work with a SQL server connection but on the mysql database it doesn't seem to work and I think its because the token is looking for just a date and the field has date and time so it gets confused. I'm trying to convert the field within the query so that it ignores the time stamp in the field and only returns values for the specific dates that I enter in through the @request token.Ryan A Nelsch |
|
|
|
rnelsch
Starting Member
31 Posts |
Posted - 2012-06-05 : 13:54:22
|
| I ended up changing my where clause to:Where `call`.StartTime <> '@Request.dteSelectBeginningDate~' and '@Request.dteSelectEndingDate~' And adding the "<>" seems to ignore the timestamp.Ryan A NelschI lied, that didn't work, it returned the entire data set. |
|
|
|
rnelsch
Starting Member
31 Posts |
Posted - 2012-06-05 : 17:58:49
|
| I figured it out, this is resolved. Thanks for the help.Ryan A Nelsch |
|
|
|
|
Date Time Conversion to Just 'Date'