Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

CAST CONVERT doesn't work Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 1:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
That exact query returns over 13000 rows that fail the isDate check?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1433037
Posted Wednesday, March 20, 2013 1:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:03 AM
Points: 6, Visits: 32
That's correct, and the reason why I use CAST CONVERT
Post #1433044
Posted Wednesday, March 20, 2013 2:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
The thing is, 0 can't be cast to a date.

DECLARE @SomeDate CHAR(8) = '0'
SELECT ISDATE(@SomeDate), CAST(@SomeDate AS DATE)

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.


So if you have thousands of rows with a 0 in, you will get conversion error.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1433049
Posted Wednesday, March 20, 2013 2:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:03 AM
Points: 6, Visits: 32
Sorry, I frogot to enter the time range in my query. If I enter it, the result nothing to display
Post #1433055
Posted Wednesday, March 20, 2013 7:35 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:14 AM
Points: 781, Visits: 244
If you add in your original query in the where statement 'And EZACDT <> 0' if it will work. If the MO's don't get closed you will have 0 in that date field. I did your Cast(Convert statement against my CINACC table and don't have an issue as long as I filter out the records with 0 in the EZACDT field.
Post #1433203
Posted Wednesday, March 20, 2013 8:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
DECLARE @SomeDate CHAR(8) = '0'
SELECT
ISDATE(@SomeDate),
CAST(NULLIF(@SomeDate,'0') AS DATE)


-- similar to this handy trick:
DECLARE @numerator INT = 1, @denominator INT = 0

SELECT @numerator/@denominator
--Msg 8134, Level 16, State 1, Line 3
--Divide by zero error encountered.

SELECT @numerator/NULLIF(@denominator,0)
-- returns NULL, no error



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1433258
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse