April 24, 2018 at 4:10 pm
I have a date column which isn't behaving as expected...
--Determining When Students Enrol and Cancel
Select (ContactEnrollDate)
From PersonalProfiles
Where ContactEnrollDate like '%2006%' ---06-17%' and
(ContactEnrollDate) is not null and ContactCancelDate is not null and LastAttended is not null
This returns 52 results, which is correct. Here are 2 of them.....
2006-03-27 00:00:00.000
2006-12-08 00:00:00.000
However if I just want those students who enrolled in March/December etc, and change the Where clause accordingly:
Where ContactEnrollDate like '2006-03-27%' or Where ContactEnrollDate like '2006-12-08%'
zero rows are returned DESPITE the fact I've seen them in the former result set.
Thanks
JB
April 24, 2018 at 4:32 pm
If the column is a data-type of datetime, I wouldn't suggest using LIKE expressions. You can use something alone the lines of WHERE YEAR(ContractEnrollDate) = 2006 AND (MONTH(ContractEnrollDate) in (3,12)).
That should give you what you are looking for.
April 24, 2018 at 4:33 pm
One way to do this is to use a Calendar table, then you can filter for Month/Year.
Otherwise, you'd have to use something like:
WHERE [EventDate] >= '01-Jan-2018' AND [EventDate] <='31-Mar-2018'
April 24, 2018 at 5:10 pm
JaybeeSQL - Tuesday, April 24, 2018 4:10 PMHi all,I have a date column which isn't behaving as expected...
--Determining When Students Enrol and Cancel
Select (ContactEnrollDate)
From PersonalProfiles
Where ContactEnrollDate like '%2006%' ---06-17%' and
(ContactEnrollDate) is not null and ContactCancelDate is not null and LastAttended is not nullThis returns 52 results, which is correct. Here are 2 of them.....
2006-03-27 00:00:00.000
2006-12-08 00:00:00.000However if I just want those students who enrolled in March/December etc, and change the Where clause accordingly:
Where ContactEnrollDate like '2006-03-27%' or Where ContactEnrollDate like '2006-12-08%'zero rows are returned DESPITE the fact I've seen them in the former result set.
Thanks
JB
Is your date column defined as a datetime or varchar(somevalue)?
April 26, 2018 at 4:09 pm
logitestus - Tuesday, April 24, 2018 4:32 PMIf the column is a data-type of datetime, I wouldn't suggest using LIKE expressions. You can use something alone the lines ofWHERE YEAR(ContractEnrollDate) = 2006 AND (MONTH(ContractEnrollDate) in (3,12)).That should give you what you are looking for.
Completely agree, and my own research verifies it should, but nonetheless...
"An expression of non-boolean type specified in a context where a condition is expected, near 'ContractEnrollDate'."
The column is of datatype 'Datetime'.
April 26, 2018 at 4:20 pm
JaybeeSQL - Thursday, April 26, 2018 4:09 PMlogitestus - Tuesday, April 24, 2018 4:32 PMIf the column is a data-type of datetime, I wouldn't suggest using LIKE expressions. You can use something alone the lines ofWHERE YEAR(ContractEnrollDate) = 2006 AND (MONTH(ContractEnrollDate) in (3,12)).That should give you what you are looking for.
Completely agree, and my own research verifies it should, but nonetheless...
"An expression of non-boolean type specified in a context where a condition is expected, near 'ContractEnrollDate'."
The column is of datatype 'Datetime'.
Post your code that is getting the error.
April 26, 2018 at 5:00 pm
Lynn Pettis - Thursday, April 26, 2018 4:20 PMPost your code that is getting the error.
--Determining When Students Enrol and Cancel
Select (ContactEnrollDate)
From PersonalProfiles
WHERE YEAR ContractEnrollDate = 2006 AND (MONTH ContractEnrollDate in (3,12))
and (ContactEnrollDate) is not null and ContactCancelDate is not null and LastAttended is not null
April 26, 2018 at 5:07 pm
...and post the CREATE TABLE script for the 'PersonalProfiles' table... just want to make sure that you've defined the columns properly. Using the wrong datatype could cause unexpected results.
April 26, 2018 at 8:08 pm
JaybeeSQL - Thursday, April 26, 2018 5:00 PMLynn Pettis - Thursday, April 26, 2018 4:20 PMPost your code that is getting the error.
--Determining When Students Enrol and Cancel
Select (ContactEnrollDate)
From PersonalProfiles
WHERE YEAR ContractEnrollDate = 2006 AND (MONTH ContractEnrollDate in (3,12))
and (ContactEnrollDate) is not null and ContactCancelDate is not null and LastAttended is not null
YEAR is a function and you are missing the () around ContractEnrollDate.
Select
ContactEnrollDate
From PersonalProfiles
WHERE
YEAR(ContractEnrollDate) = 2006
AND (MONTH (ContractEnrollDate) in (3,12))
AND (ContactEnrollDate) is not null
AND ContactCancelDate is not null
AND LastAttended is not null ;
April 27, 2018 at 4:53 am
pietlinden - Thursday, April 26, 2018 5:07 PM...and post the CREATE TABLE script for the 'PersonalProfiles' table... just want to make sure that you've defined the columns properly. Using the wrong datatype could cause unexpected results.
(edit: Lynn's latest script gives the errors:
Msg 207, Level 16, State 1, Line 515
Invalid column name 'ContractEnrollDate'.
Msg 207, Level 16, State 1, Line 516
Invalid column name 'ContractEnrollDate'.
Msg 207, Level 16, State 1, Line 516
Invalid column name 'ContractEnrollDate'. )
CREATE TABLE [dbo].[personalprofiles](
[id] [int] NOT NULL,
[alt_id] [nvarchar](50) NULL,
[birthday] [datetime] NULL,
[sex] [nvarchar](10) NULL,
[salutation] [nvarchar](50) NULL,
[lname] [nvarchar](50) NULL,
[fname] [nvarchar](50) NULL,
[lname_cont1] [nvarchar](50) NULL,
[fname_cont1] [nvarchar](50) NULL,
[phone_cont1] [nvarchar](50) NULL,
[rel_cont1] [nvarchar](50) NULL,
[lname_cont2] [nvarchar](50) NULL,
[fname_cont2] [nvarchar](50) NULL,
[phone_cont2] [nvarchar](50) NULL,
[rel_cont2] [nvarchar](50) NULL,
[address] [nvarchar](100) NULL,
[city] [nvarchar](50) NULL,
[state] [nvarchar](50) NULL,
[zip] [nvarchar](50) NULL,
[nvarchar](255) NULL,
[webpage] [nvarchar](255) NULL,
[phone1] [nvarchar](50) NULL,
[phone2] [nvarchar](50) NULL,
[phone3] [nvarchar](50) NULL,
[fax] [nvarchar](50) NULL,
[pager] [nvarchar](50) NULL,
[weight] [nvarchar](50) NULL,
[company] [nvarchar](255) NULL,
[jobtitle] [nvarchar](255) NULL,
[categoryid] [int] NULL,
[custom1] [nvarchar](255) NULL,
[custom2] [nvarchar](255) NULL,
[custom3] [nvarchar](255) NULL,
[custom4] [nvarchar](255) NULL,
[custom5] [nvarchar](255) NULL,
[custom6] [nvarchar](255) NULL,
[custom7] [nvarchar](255) NULL,
[custom8] [nvarchar](255) NULL,
[custom9] [nvarchar](255) NULL,
[custom10] [nvarchar](255) NULL,
[cmethod] [nvarchar](30) NULL,
[firstcontact] [datetime] NULL,
[referredby] [nvarchar](150) NULL,
[sourceid] [int] NULL,
[prevexpid] [int] NULL,
[styleid] [int] NULL,
[objectivesid] [int] NULL,
[freelessons] [datetime] NULL,
[rankid] [int] NULL,
[nextfollowup] [datetime] NULL,
[employeeid] [int] NULL,
[medical] [nvarchar](50) NULL,
[instructorid] [int] NULL,
[class1id] [int] NULL,
[class2id] [int] NULL,
[class3id] [int] NULL,
[class4id] [int] NULL,
[beltsize] [nvarchar](50) NULL,
[rating] [nvarchar](50) NULL,
[stripe1] [datetime] NULL,
[stripe2] [datetime] NULL,
[stripe3] [datetime] NULL,
[stripe4] [datetime] NULL,
[stripe5] [datetime] NULL,
[stripe6] [datetime] NULL,
[nextexam] [datetime] NULL,
[memberof1] [nvarchar](50) NULL,
[memberof2] [nvarchar](50) NULL,
[memberof3] [nvarchar](50) NULL,
[call2] [datetime] NULL,
[call4] [datetime] NULL,
[call6] [datetime] NULL,
[bbcmccand] [datetime] NULL,
[feedback] [datetime] NULL,
[renewalconf] [datetime] NULL,
[financestatus] [nvarchar](50) NULL,
[finagrno] [int] NULL,
[User] [int] NULL,
[CancelledDate] [datetime] NULL,
[totalattend] [smallint] NULL,
[totalattend1] [smallint] NULL,
[Class5id] [int] NULL,
[Class6id] [int] NULL,
[Class7id] [int] NULL,
[Class8id] [int] NULL,
[Class9id] [int] NULL,
[Class10id] [int] NULL,
[EFTAccount] [nvarchar](20) NULL,
[totalattend2] [smallint] NULL,
[Modified] [datetime] NULL,
[MVid] [int] NULL,
[StripesReceived] [smallint] NULL,
[ImageModifiedDate] [datetime] NULL,
[lastExam] [datetime] NULL,
[SMS] [nvarchar](40) NULL,
[hasPhoto] [nvarchar](5) NULL,
[custom11] [nvarchar](255) NULL,
[custom12] [nvarchar](255) NULL,
[country] [nvarchar](50) NULL,
[stripe7] [datetime] NULL,
[stripe8] [datetime] NULL,
[ContactCancelDate] [datetime] NULL,
[ContactEnrollDate] [datetime] NULL,
[LastAttended] [datetime] NULL,
[NextRank] [nvarchar](80) NULL,
[RF_ID] [nvarchar](50) NULL
) ON [PRIMARY]
April 27, 2018 at 8:24 am
JaybeeSQL - Friday, April 27, 2018 4:53 AMpietlinden - Thursday, April 26, 2018 5:07 PM...and post the CREATE TABLE script for the 'PersonalProfiles' table... just want to make sure that you've defined the columns properly. Using the wrong datatype could cause unexpected results.I didn't define the column, I just inherited it (though I can change it as I'm just running reports from this copy). Plus, it's a crappy design (whither normalisation??) 🙂 the [[ContactEnrollDate] column plus the two other 'Where' filters are near the bottom...(edit: Lynn's latest script gives the errors:
Msg 207, Level 16, State 1, Line 515
Invalid column name 'ContractEnrollDate'.
Msg 207, Level 16, State 1, Line 516
Invalid column name 'ContractEnrollDate'.
Msg 207, Level 16, State 1, Line 516
Invalid column name 'ContractEnrollDate'. )CREATE TABLE [dbo].[personalprofiles](
[id] [int] NOT NULL,
[alt_id] [nvarchar](50) NULL,
[birthday] [datetime] NULL,
[sex] [nvarchar](10) NULL,
[salutation] [nvarchar](50) NULL,
[lname] [nvarchar](50) NULL,
[fname] [nvarchar](50) NULL,
[lname_cont1] [nvarchar](50) NULL,
[fname_cont1] [nvarchar](50) NULL,
[phone_cont1] [nvarchar](50) NULL,
[rel_cont1] [nvarchar](50) NULL,
[lname_cont2] [nvarchar](50) NULL,
[fname_cont2] [nvarchar](50) NULL,
[phone_cont2] [nvarchar](50) NULL,
[rel_cont2] [nvarchar](50) NULL,
[address] [nvarchar](100) NULL,
[city] [nvarchar](50) NULL,
[state] [nvarchar](50) NULL,
[zip] [nvarchar](50) NULL,
[nvarchar](255) NULL,
[webpage] [nvarchar](255) NULL,
[phone1] [nvarchar](50) NULL,
[phone2] [nvarchar](50) NULL,
[phone3] [nvarchar](50) NULL,
[fax] [nvarchar](50) NULL,
[pager] [nvarchar](50) NULL,
[weight] [nvarchar](50) NULL,
[company] [nvarchar](255) NULL,
[jobtitle] [nvarchar](255) NULL,
[categoryid] [int] NULL,
[custom1] [nvarchar](255) NULL,
[custom2] [nvarchar](255) NULL,
[custom3] [nvarchar](255) NULL,
[custom4] [nvarchar](255) NULL,
[custom5] [nvarchar](255) NULL,
[custom6] [nvarchar](255) NULL,
[custom7] [nvarchar](255) NULL,
[custom8] [nvarchar](255) NULL,
[custom9] [nvarchar](255) NULL,
[custom10] [nvarchar](255) NULL,
[cmethod] [nvarchar](30) NULL,
[firstcontact] [datetime] NULL,
[referredby] [nvarchar](150) NULL,
[sourceid] [int] NULL,
[prevexpid] [int] NULL,
[styleid] [int] NULL,
[objectivesid] [int] NULL,
[freelessons] [datetime] NULL,
[rankid] [int] NULL,
[nextfollowup] [datetime] NULL,
[employeeid] [int] NULL,
[medical] [nvarchar](50) NULL,
[instructorid] [int] NULL,
[class1id] [int] NULL,
[class2id] [int] NULL,
[class3id] [int] NULL,
[class4id] [int] NULL,
[beltsize] [nvarchar](50) NULL,
[rating] [nvarchar](50) NULL,
[stripe1] [datetime] NULL,
[stripe2] [datetime] NULL,
[stripe3] [datetime] NULL,
[stripe4] [datetime] NULL,
[stripe5] [datetime] NULL,
[stripe6] [datetime] NULL,
[nextexam] [datetime] NULL,
[memberof1] [nvarchar](50) NULL,
[memberof2] [nvarchar](50) NULL,
[memberof3] [nvarchar](50) NULL,
[call2] [datetime] NULL,
[call4] [datetime] NULL,
[call6] [datetime] NULL,
[bbcmccand] [datetime] NULL,
[feedback] [datetime] NULL,
[renewalconf] [datetime] NULL,
[financestatus] [nvarchar](50) NULL,
[finagrno] [int] NULL,
[User] [int] NULL,
[CancelledDate] [datetime] NULL,
[totalattend] [smallint] NULL,
[totalattend1] [smallint] NULL,
[Class5id] [int] NULL,
[Class6id] [int] NULL,
[Class7id] [int] NULL,
[Class8id] [int] NULL,
[Class9id] [int] NULL,
[Class10id] [int] NULL,
[EFTAccount] [nvarchar](20) NULL,
[totalattend2] [smallint] NULL,
[Modified] [datetime] NULL,
[MVid] [int] NULL,
[StripesReceived] [smallint] NULL,
[ImageModifiedDate] [datetime] NULL,
[lastExam] [datetime] NULL,
[SMS] [nvarchar](40) NULL,
[hasPhoto] [nvarchar](5) NULL,
[custom11] [nvarchar](255) NULL,
[custom12] [nvarchar](255) NULL,
[country] [nvarchar](50) NULL,
[stripe7] [datetime] NULL,
[stripe8] [datetime] NULL,
[ContactCancelDate] [datetime] NULL,
[ContactEnrollDate] [datetime] NULL,
[LastAttended] [datetime] NULL,
[NextRank] [nvarchar](80) NULL,
[RF_ID] [nvarchar](50) NULL
) ON [PRIMARY]GO
Don't blame my code, I used what you posted.
April 27, 2018 at 9:06 am
Let's just fix it:SELECT ContactEnrollDate
FROM PersonalProfiles
WHERE YEAR(ContactEnrollDate) = 2006
AND MONTH(ContactEnrollDate) IN (3,12)
AND ContactCancelDate IS NOT NULL
AND LastAttended IS NOT NULL;
No reason to add a condition for ContactEnrollDate to check for NULL when that will be met merely by having other conditions on it that specify values.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 27, 2018 at 9:34 am
Don't use functions on any column unless you have absolutely no choice. In other words, keep the condition sargable if at all possible
--If you want the months of Mar 2006 and Dec 2006, then this:
WHERE ((ContactEnrollDate >= '20060301' AND ContractEntrollDate < '20060401') OR
(ContactEnrollDate >= '20061201' AND ContractEntrollDate < '20070101'))
--For a specific day in Mar or Dec, then:
WHERE ((ContactEnrollDate >= '20060327' AND ContractEntrollDate < '20060328') OR
(ContactEnrollDate >= '20061208' AND ContractEntrollDate < '20061209'))
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 27, 2018 at 6:39 pm
sgmunson - Friday, April 27, 2018 9:06 AMLet's just fix it:SELECT ContactEnrollDate
FROM PersonalProfiles
WHERE YEAR(ContactEnrollDate) = 2006
AND MONTH(ContactEnrollDate) IN (3,12)
AND ContactCancelDate IS NOT NULL
AND LastAttended IS NOT NULL;No reason to add a condition for ContactEnrollDate to check for NULL when that will be met merely by having other conditions on it that specify values.
Curious, that too does not return any rows...
April 27, 2018 at 6:50 pm
ScottPletcher - Friday, April 27, 2018 9:34 AMDon't use functions on any column unless you have absolutely no choice. In other words, keep the condition sargable if at all possible
--If you want the months of Mar 2006 and Dec 2006, then this:
WHERE ((ContactEnrollDate >= '20060301' AND ContractEntrollDate < '20060401') OR
(ContactEnrollDate >= '20061201' AND ContractEntrollDate < '20070101'))--For a specific day in Mar or Dec, then:
WHERE ((ContactEnrollDate >= '20060327' AND ContractEntrollDate < '20060328') OR
(ContactEnrollDate >= '20061208' AND ContractEntrollDate < '20061209'))
That, after I cleaned up the final column-name on each line, (i wonder if you just wanted to see if I was still awake 🙂 did the trick -
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply