| Author |
Topic |
|
Jannette
Starting Member
26 Posts |
 Posted - 2011-08-25 : 08:31:47
|
| I have the following query, but regardless of my where clause that should only select records with a stat date greater than or equal to '01.04.2011. This is being ignored. Hope someone can help. Thanks in advance.Select Distinct '""', '"'+Candidate.Candidate_Code+'"', '""', '"'+Person.Last_Name+'"', '"'+Person.First_Name+'"', '""', '""', '"'+Person.Title+'"', '"'+Person.Salutation+'"', '"'+Address.Line_1+'"', '"'+Address.Line_2+'"', '"'+Address.Line_3+'"', '"'+Town.Description+'"', '"'+County.Description+'"', '"'+Address.Postcode+'"', '"'+Country.Description+'"', '"'+CONTACT_INFO.Phone_Code_1+' '+CONTACT_INFO.Phone_Number_1+'"', '"'+PERSON.Gender+'"', '"'+CONVERT(VARCHAR(10),DOB,103)+'"', '"'+'U'+'"', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', '""', Case When PAYE.PAYE_Start_DT is Null then '"'+'01/10/2011'+'"' Else '"'+CONVERT(VARCHAR(10),PAYE.PAYE_Start_DT,103)+'"' End as START_DT, '""', '""', '""', '"'+SUBSTRING(CANDIDATE.Notes,1,300)+'"', '""', '""', '"'+CONTACT_INFO.Mobile_Code+' '+CONTACT_INFO.Mobile_Number+'"', '"'+CONTACT_INFO.Email_Address+'"', Case When PAYE.NINO is Null then '""' Else '"'+PAYE.NINO+'"' End, Case When PAYE.NI_Table is Null then '""' Else '"'+PAYE.NI_Table+'"' End, '""', Case When PAYE.Tax_Code is Null then '""' Else '"'+PAYE.Tax_Code+'"' End, Case When PAYE.Wk1Mn1_YN = 'Y' Then 1 Else 0 End, '"'+PAYMENT_METHOD.Type+'"', Case When BANK_BRANCH.Sort_Code is Null then '""' Else '"'+BANK_BRANCH.Sort_Code+'"' End, Case When PERSON.Bank_Account_No is Null then '""' Else '"'+PERSON.Bank_Account_No+'"' End, Case When PERSON.Bank_Account_Name is Null then '""' Else '"'+PERSON.Bank_Account_Name+'"' End, '""', Case When PERSON.Bank_Account_Ref is Null then '""' Else '"'+PERSON.Bank_Account_Ref+'"' End, '""', '""','""', '""','""', '""','""', '""','""', '""','""', Case When PAYE.SLD_YN is Null then 'N' Else '"'+PAYE.SLD_YN+'"' End, '""', '""', '""', '""','""', '""','""', Case When CANDIDATE.Candidate_Type = 'C' Then '"'+'U'+'"' When CANDIDATE.Candidate_Type = 'S' Then '"'+'C'+'"' Else '"'+'P'+'"' End, '""', '""','""', '""','""', '""', Case When PAYE.Payslip_Type is NULL then '"P"' Else '"'+Substring(PAYE.Payslip_Type,1,1)+'"' End, '""'From CANDIDATE Inner Join Person ON CANDIDATE.Person_Id = PERSON.Person_Id Inner Join ADDRESS ON PERSON.Address_Id = ADDRESS.Address_Id Inner Join TOWN ON Address.Town_Id = TOWN.Town_Id Inner Join COUNTY ON ADDRESS.County_Id = COUNTY.County_Id Inner Join COUNTRY ON ADDRESS.Country_Id = COUNTRY.Country_Id Inner Join CONTACT_INFO ON PERSON.Contact_Info_Id = CONTACT_INFO.Contact_Info_Id Inner Join PAYMENT_METHOD ON PERSON.Payment_Method_Id = PAYMENT_METHOD.Payment_Method_Id Left Outer Join BANK_BRANCH ON PERSON.Branch_Id = BANK_BRANCH.Branch_Id Left Outer Join PAYE ON Person.Person_Id = PAYE.Person_Id Left Join PAYSLIP on PAYE.Paye_Id = PAYSLIP.Paye_Id Left Join PAYSLIP_P11 On PAYSLIP.Payslip_Id = PAYSLIP_P11.Payslip_Id Where (Candidate.Active_YN = 'Y') or (Candidate.Active_YN = 'N' and CONVERT(VARCHAR(10),PAYE.PAYE_Start_DT,103) >= '01/04/2011') |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-08-25 : 08:49:56
|
Where Candidate.Active_YN = 'Y'or (Candidate.Active_YN = 'N' and PAYE.PAYE_Start_DT >= '20110401' --- ISO format!
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-25 : 09:17:17
|
| "CONVERT(VARCHAR(10),PAYE.PAYE_Start_DT,103) >= '01/04/2011'"is making an alphabetic comparison of the dates. Always compare dates using native DATETIME datatype.SQL will make an implicit conversion of string-to-datetime if you use ISO format, but in case it clarifies Peso's example a bit:PAYE.PAYE_Start_DT >= '20110401'can be rewritten with explicit datetime conversion as:PAYE.PAYE_Start_DT >= CONVERT(datetime, '20110401') |
|
|
|
|
|
|
Problems with dates