Good morning. I have the above issue with the following code.
SELECT c.Account_SPID__c [SPID]
,CONVERT(DATE,c.Background_Check_Authorization_Date_Sent__c) [Sent]
,CONVERT(DATE,c.BGC_Completion_Date__c) [Complete]
,c.BGC_Status__c [BGC Status]
,m2.market
,a.billingstate
FROM SalesForceLocal_Reporting.dbo.Contact c WITH (NOLOCK)
inner join SalesForceLocal.dbo.Account a with (nolock) on c.account_spid__C = a.spid__C
inner join SalesForceLocal.dbo.market__C m with (nolock) on a.market__C = m.id
inner join angie.dbo.markets m2 with (nolock) on m.market_id__C = m2.marketid
where c.Background_Check_Authorization_Date_Sent__c >= DATEADD(year,-1,GETDATE())
and c.BGC_Status__c in ('pass','fail')
and c.BGC_Completion_Date__c is not null
and c.BGC_Completion_Date__c >= c.Background_Check_Authorization_Date_Sent__c
and c.background_check_authorization_date_sent__C not in
('2023-04-18 10:45:00:00000')
If I leave out the final check for excluding that specific time stamp, it runs fine. But I actually need to exclude all entries from that time. So, the issue is somewhere within entries on that date, but I have been unable to find the malformed string due to the nature of the query, e.g. It is validating what dates are needed, but fails when it gets to the corrupted/malformed entry. If I leave the date verification out in the "where" statement, I get 4M results.
Thanks for any help!
Assuming SQL Server based on the text of the error, I was able to replicate the issue:
In short, there are too many 0's at the end of the date literal, and you should do this instead:
and c.background_check_authorization_date_sent__C not in
('2023-04-18T10:45:00.000')
Also note the extra T between the date and time portions, which really is correct for SQL Server in a way the original is not. If you preferred, you could also do it like this:
and c.background_check_authorization_date_sent__C not in
('20230418 10:45:00.000')
but again: the original format will eventually get you in trouble and you should avoid it.