September 19, 2016 at 11:44 am
Got a Column FundingDate DATETIME ( has date as well as timestamp). I am using the following in where clause.
CONVERT(varchar(10), L.FundingDate, 101) <=CONVERT(VARCHAR(10),GETDATE()-90,101)
--doesn't work, misses dates such 2007-11-14 15:05:13.260 , 2012-10-09 13:46:54.380
L.FundingDate <= CONVERT(VARCHAR(10),GETDATE()-90,101) --this works includes the above mentioned dates as well
shouldn't the first statement be the correct one, as it would be better to convert L.FundingDate
What would be the best way to go forward
September 19, 2016 at 1:12 pm
SQLPain (9/19/2016)
Got a Column FundingDate DATETIME ( has date as well as timestamp). I am using the following in where clause.
CONVERT(varchar(10), L.FundingDate, 101) <=CONVERT(VARCHAR(10),GETDATE()-90,101)--doesn't work, misses dates such 2007-11-14 15:05:13.260 , 2012-10-09 13:46:54.380
L.FundingDate <= CONVERT(VARCHAR(10),GETDATE()-90,101) --this works includes the above mentioned dates as well
shouldn't the first statement be the correct one, as it would be better to convert L.FundingDate
What would be the best way to go forward
Quick thought, don't convert the column as that will wreck the performance, here are two examples on how to do this.
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @SEARCH_DATE DATETIME = '2016-09-19 20:00:02.550';
DECLARE @NUM_DAYS INT = 90;
;WITH SAMPLE_DATA(SD_ID,SD_DATE) AS
(
SELECT SD_ID,SD_DATE FROM
(VALUES (1,'2016-06-21 04:06:05.997')
,(2,'2016-06-20 04:06:37.783')
,(3,'2016-09-19 20:00:02.550')
,(4,'2016-01-18 04:02:09.593')
,(5,'2016-06-22 04:05:42.230')
)AS X(SD_ID,SD_DATE)
)
SELECT
SD.SD_ID
,SD.SD_DATE
FROM SAMPLE_DATA SD
WHERE SD.SD_DATE > CONVERT(DATETIME,CONVERT(DATE,@SEARCH_DATE - CONVERT(DATETIME,@NUM_DAYS,0),0),0);
;WITH SAMPLE_DATA(SD_ID,SD_DATE) AS
(
SELECT SD_ID,SD_DATE FROM
(VALUES (1,'2016-06-21 04:06:05.997')
,(2,'2016-06-20 04:06:37.783')
,(3,'2016-09-19 20:00:02.550')
,(4,'2016-01-18 04:02:09.593')
,(5,'2016-06-22 04:05:42.230')
)AS X(SD_ID,SD_DATE)
)
SELECT
SD.SD_ID
,SD.SD_DATE
FROM SAMPLE_DATA SD
WHERE SD.SD_DATE > CONVERT(DATETIME,CONVERT(DATE,DATEADD(DAY,-@NUM_DAYS,@SEARCH_DATE),0),0);
September 19, 2016 at 2:08 pm
I am converting getdate to varchar but not converting L.fundingDate to varchar, would that miss out any dates. converting both to varchar is not giving me the accurate results somehow. the following is working out fine, is this the right thing to do?
L.FundingDate <= CONVERT(VARCHAR(10),GETDATE()-30,101)
September 19, 2016 at 2:48 pm
When you convert both datetime values to varchar, you're doing a string comparison.
The format you've chosen in the convert function converts the datetime to string in the format 'mm/dd/yyyy'.
Doing a string comparison between two strings that represent dates with the leading characters of the string being for the month will lead to incorrect results when checking whether one is less than the other (for example, as a string, '11/10/1800' is greater than '10/10/2100').
When you only convert one of the values being compared to varchar, then it gets implicitly converted back to datetime, because datetime has higher data type precedence (see https://msdn.microsoft.com/en-us/library/ms190309.aspx).
That's why you get expected behavior then.
You're wanting to compare dates, not strings, so I'd avoid converting to a string altogether.
Eirikur gave some examples of ways to do this. Another would be to do a DATEDIFF(dd,...) between the two dates and use that as your criterion.
Cheers!
September 19, 2016 at 3:16 pm
SQLPain (9/19/2016)
I am converting getdate to varchar but not converting L.fundingDate to varchar, would that miss out any dates. converting both to varchar is not giving me the accurate results somehow. the following is working out fine, is this the right thing to do?
L.FundingDate <= CONVERT(VARCHAR(10),GETDATE()-30,101)
As I mentioned in your thread % of total count, you should be CASTing these as date rather than converting them to a string. Converting back and forth between datetime and string is horrible on performance. You're not noticing it, because you're only doing it on constants and variables right now, but you don't want to get in the habit.
L.FundingDate <= CAST(GETDATE()-30 AS DATE)
Drew
Edit: removed extraneous word left over from previous phrasing.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 19, 2016 at 3:31 pm
What Eirikur is trying to help you avoid is called a non-SARGable predicate. It means that your WHERE clause predicate cannot use an index seek on your FundingDate column, even if the perfect index exists. An index would contain the date, not the result of your function.
Another point to consider is what you're trying to do. Generally speaking, if you perform a function on a column, the function has to be calculated for every row in the table before the predicate can be applied. So, if you have 1,000,000 rows in your table, that's 1,000,000 date calculations that have to be performed before the filter is applied. This leads to performance disasters, especially as your data grows.
By using the value in table (or NCI) and comparing it against a calculated date relative to today, you can avoid these performance problems.
And please heed the warnings about converting the datetime to any type of string. SQL Server is quite good at date math, but you render it useless when you start converting it to other data types. My advice is to stick with the datetime data type as Eirikur and others have suggested.
September 20, 2016 at 9:47 am
Thanks Everyone.
So basically because of the precedence rule the following would end up giving me the same result?
L.FundingDate <= CONVERT(VARCHAR(10),GETDATE()-30,101)
L.FundingDate <= GETDATE()-30
Also would the following give out the same results?
L.FundingDate <= CAST(GETDATE()-30 AS DATE)
SELECT CAST(L.FundingDate AS DATE) <= CAST(GETDATE()-30 AS DATE)
Somebody mentioned using DATEDIFF in my where clause.
September 20, 2016 at 9:55 am
Also when I am using parameters like below in the where clause: mostly used in SSRS reports drop down date parameter.
AND CONVERT(varchar(20), CA.when_funded, 101) >= @StartDate
AND CONVERT(varchar(20), CA.when_funded, 101) <= @EndDate
Does converting when_funded make any sense? Can this lead to a wrong result set? for example in my drop down menu I select 10/01/2015 for StartDate and 02/01/2016 for EndDate.
Would casting be a better option here as well?
September 20, 2016 at 10:01 am
SQLPain (9/20/2016)
Thanks Everyone.So basically because of the precedence rule the following would end up giving me the same result?
L.FundingDate <= CONVERT(VARCHAR(10),GETDATE()-30,101)
L.FundingDate <= GETDATE()-30
Also would the following give out the same results?
L.FundingDate <= CAST(GETDATE()-30 AS DATE)SELECT CAST(L.FundingDate AS DATE) <= CAST(GETDATE()-30 AS DATE)
Somebody mentioned using DATEDIFF in my where clause.
I only mentioned that possibility for completeness.
Doing the direct comparison to the column value is preferable, but be careful with the example you gave of GETDATE()-30. That will preserve the time portion, which I'm assuming you wanted to remove.
Cheers!
September 20, 2016 at 10:09 am
Thanks, so L.FundingDate <= CAST(GETDATE()-30 AS DATE) that's the best way to do it.
September 20, 2016 at 10:16 am
If that is the correct logic for your requirements, that should work as well as anything else.
On that note, if you run the report right now, what is the latest datetime entry you would want to appear in the result set?
As written, right now that would include midnight of 20160821.
Cheers!
September 20, 2016 at 10:33 am
I would like to see the records from todays date as well.
September 20, 2016 at 10:38 am
SQLPain (9/20/2016)
I would like to see the records from todays date as well.
Eh? How would you expect that? The filter is for anything less than or equal to the date 30 days ago (or 90 days ago in the original post).
You'll probably need to show the full query and explain the business logic for that.
I was mostly trying to point out that "less than or equal" might not yield the results you expected.
Cheers!
September 20, 2016 at 10:40 am
Similarly :
WHERE CONVERT(varchar(20), H.TransactionDate, 101) >= @StartDate
AND CONVERT(varchar(20), H.TransactionDate, 101) <= @EndDate
--Better would be:
WHERE CAST(H.TransactionDate AS DATE) >= @StartDate
AND CAST( H.TransactionDate AS DATE) <= @EndDate
Do we also need to cast @StartDate and @EndDate ?
September 20, 2016 at 10:44 am
You are right. I mixed up couple of things here
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply