Thank you for your suggestions.
I have the DATEFORMAT set correctly and validated with dbcc useroptions.
I don't have any date fields stored as character strings. All date fields in the source and target are smalldatetime.
The data are loaded from external sources to the original source table using a cast from datetime to smalldatetime:
CASE WHEN ISDATE(CAST([Inception Date] AS smalldatetime)) = 1
THEN CAST([Inception Date] AS smalldatetime)
ELSE NULL
END
The [Inception Date] is of datetime type.
All other fields in the source and target are nvarchar, without exception; There are no fields that could be interpreted as date strings.
I am very puzzled; I have paid due diligence about data typing.
Any suggestions are very welcome.
Thank you very much for your time.
I think you have to explicitly make your NULLs smalldatetime:
CASE
WHEN ISDATE(CAST([Inception Date] AS smalldatetime)) = 1
THEN CAST([Inception Date] AS smalldatetime)
ELSE CAST(NULL AS smalldatetime)
END
Hi: thank you for the suggestion; I re-ran with the correction; alas this did not resolve the issue. Thank you very much for your time.
For casting to smalldatetime to work:
1. The date range must be 1900-01-01 to 2079-06-06
2. There must be no more than 3 decimal places with the seconds.
Check you data to confirm this.
christine.c.ringleb (7/16/2014)
Thank you for your suggestions.I have the DATEFORMAT set correctly and validated with dbcc useroptions.
I don't have any date fields stored as character strings. All date fields in the source and target are smalldatetime.
The data are loaded from external sources to the original source table using a cast from datetime to smalldatetime:
CASE WHEN ISDATE(CAST([Inception Date] AS smalldatetime)) = 1
THEN CAST([Inception Date] AS smalldatetime)
ELSE NULL
END
The [Inception Date] is of datetime type.
All other fields in the source and target are nvarchar, without exception; There are no fields that could be interpreted as date strings.
I am very puzzled; I have paid due diligence about data typing.
Any suggestions are very welcome.
Thank you very much for your time.
So, if [Inception Date] is a smalldatetime, the call to ISDATE() will involve an implicit conversion from smalldatetime to a string data type as described in the MSDN page for ISDATE : http://msdn.microsoft.com/en-gb/library/ms187347(v=sql.100).aspx
Topic link iconTransact-SQL Syntax Conventions
Syntax
ISDATE (expression )
Arguments
expression
Is a character string or expression that can be converted to a character string. The expression must be less than 4,000 characters.
And this is being done why? If it is already a smalldatetime, then the test is irrelevant at best?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Quite right: if DFUR1.[AsOfDate] is any date/datetime type, you don't want to use ISDATE() on it.
Also, you can simplify the year and month comparisons:
AND
YEAR(MD1.[Inception Date]) = YEAR(DFUR1.[Fund Inception Date])
AND
MONTH(MD1.[Inception Date]) = MONTH(DFUR1.[Fund Inception Date])
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".
I will make this changes tomorrow; we have some other deliverable to work on today. Thank you for your suggestions.
Hi: I finally found time to look at it; the error was only a "datetime to string" error because the # of columns in the Select were not identical to the # of columns in the Insert Into -> a mere typo :). Thank you
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply