Datetime conversion issue

• christine.c.ringleb

  Valued Member

  Points: 71

  More actions

  July 15, 2014 at 4:04 pm

  #293489

  Hi:

  I would be very grateful for your help with the following query. All source and target date fields are defined as data type "smalldatetime". The "select" executes without error though when used with "insert into" it fails with the error:

  Msg 295, Level 16, State 3, Line 25

  Conversion failed when converting character string to smalldatetime data type

  I am not aware that I am converting from a character string to smalldatetime since the source and target date columns are "smalldatetime". All other columns for the source and target are nvarchar(255). I assume there is an implicit conversion that I don't understand. In a test, I validated that all dates selected evaluate ISDATE() to 1.

  Here is the complete query. Thank you very much for your time and advise.

  USE [SCIR_DataMart_FromProd_06_20_2014]

  GO

  IF OBJECT_ID ('[SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]', 'U') IS NOT NULL

  DROP TABLE [SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]

  CREATE TABLE [SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]

  ( [SCIR_Key] int not null

  ,[AsOfDate] smalldatetime null

  ,[Ticker] nvarchar(255) null

  ,[Fund Name] nvarchar(255) null

  ,[FUND INCEPTION DATE] smalldatetime null

  ,[Security Type] nvarchar(255) null

  ,[OTHER FEATURES] nvarchar(255) null

  ,[MSD_SecID] nvarchar(255) null

  ,[MSD_Symbol] nvarchar(255) null

  ,[MSD_FundName] nvarchar(255) null

  ,[MSD_ISIN] nvarchar(255) null

  ,[MSD_CUSIP] nvarchar(255) null

  ,[MSD_InceptionDate] smalldatetime null

  ,[MSD_FirmName] nvarchar(255) null

  ,[MSD_BaseCurrency] nvarchar(255) null

  ,[MSD_FundID] nvarchar(255) null

  ,[MSD_FundLegalStructure] nvarchar(255) null

  )

  INSERT INTO [SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]

  (

  [SCIR_Key]

  ,[AsOfDate]

  ,[Ticker]

  ,[Fund Name]

  ,[FUND INCEPTION DATE]

  ,[Security Type]

  ,[OTHER FEATURES]

  ,[MSD_SecID]

  ,[MSD_Symbol]

  ,[MSD_FundName]

  ,[MSD_ISIN]

  ,[MSD_CUSIP]

  ,[MSD_InceptionDate]

  ,[MSD_FirmName]

  ,[MSD_BaseCurrency]

  ,[MSD_FundID]

  ,[MSD_FundLegalStructure]

  )

  SELECT

  DFUR1.[SCIR_Key]

  ,CASE WHEN ISDATE(DFUR1.[AsOfDate]) = 1

  THEN DFUR1.[AsOfDate]

  ELSE NULL

  END

  ,DFUR1.[Ticker]

  ,DFUR1.[Fund Name]

  ,CASE WHEN ISDATE(DFUR1.[FUND INCEPTION DATE]) = 1

  THEN DFUR1.[FUND INCEPTION DATE]

  ELSE NULL

  END

  ,DFUR1.[Security Type]

  ,DFUR1.[OTHER FEATURES]

  ,MD1.[SecID]

  ,MD1.[Symbol]

  ,MD1.[Name]

  ,MD1.[Firm Name]

  ,MD1.[Base Currency]

  ,MD1.[ISIN]

  ,MD1.[CUSIP]

  ,CASE WHEN ISDATE(MD1.[Inception Date]) = 1

  THEN MD1.[Inception Date]

  ELSE NULL

  END

  ,MD1.[FundID]

  ,MD1.[Fund Legal Structure]

  FROM [SCIR_DataMart_FromProd_06_20_2014].[dbo].[Mstar Monthly] DFUR1

  ,[SCIR_DataMart_FromProd_06_20_2014].[dbo].[RawMD_Combined_final] MD1

  WHERE DFUR1.[AsOfDate] = (SELECT MAX([AsOfDate]) FROM [SCIR_DataMart_FromProd_06_20_2014].[dbo].[Mstar Monthly])

  AND (

  (MD1.[Symbol] is null

  AND

  MD1.[Name] like DFUR1.[Fund Name]

  AND

  YEAR(CAST(MD1.[Inception Date] AS date)) like YEAR(CAST(DFUR1.[Fund Inception Date] AS date))

  AND

  MONTH(CAST(MD1.[Inception Date] AS date)) like MONTH(CAST(DFUR1.[Fund Inception Date] AS date))

  )

  OR

  (MD1.[Symbol] is not null

  AND

  MD1.[Symbol] = DFUR1.[Ticker]

  )

  )

  ORDER BY SCIR_Key

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  July 15, 2014 at 4:41 pm

  #1729675

  It looks like you have dates stored in strings in your database, and an implicit conversion that is failing.

  If you can modify the database to store dates in date type columns, do that.

  For the immediate problem, make sure your SET DATEFORMAT setting matches the data.

  e.g. If your dates are stored dd/MM/yy(yy) then try SET DATEFORMAT DMY;

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]