0

I have a select query that I use as part of an insert statement, but it raises the following exception:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Here is the definition of the table I want to insert to:

IF OBJECT_ID('[dbo].[tbl_crbHistory]') IS NOT NULL 
DROP TABLE [dbo].[tbl_crbHistory] 
GO
CREATE TABLE [dbo].[tbl_crbHistory] ( 
[Id]               INT              IDENTITY(1,1)   NOT NULL,
[ClientReference]  VARCHAR(50)                      NOT NULL,
[Status]           INT                              NOT NULL,
[DateAdded]        DATETIME                         NOT NULL  
            CONSTRAINT [DF__tbl_crbHi__DateA__4F8DAFFA] DEFAULT (getdate()),
CONSTRAINT   [PK_tbl_crbHistory]  PRIMARY KEY CLUSTERED    ([Id] asc),
CONSTRAINT   [FK_tbl_crbHistory_ref_crbStatus]   
            FOREIGN KEY ([Status]) REFERENCES [ref_crbStatus]([statusId]))

GO

CREATE NONCLUSTERED INDEX [idx_crbDateAdded] 
   ON [dbo].[tbl_crbHistory] ([DateAdded] desc)
   INCLUDE ([Status], [ClientReference])
CREATE NONCLUSTERED INDEX [idx_clienteReferenceStatus] 
   ON [dbo].[tbl_crbHistory] ([ClientReference] asc)
   INCLUDE ([Status], [DateAdded])
GO

EXEC sys.sp_addextendedproperty
          @name = N'MS_Description', @value = N'Stores history of CRB status change',
          @level0type = N'SCHEMA', @level0name = [dbo],
          @level1type = N'TABLE', @level1name = [tbl_crbHistory];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', @value = N'Primary Key',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [Id];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', 
         @value = N'unique id for each applicat per organisationId',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [ClientReference];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', @value = N'CRB Status',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [Status];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', @value = N'Time stamp',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [DateAdded];

the complete statement, including the select and the insert:

INSERT INTO tbl_crbHistory (clientReference, status, dateAdded)
SELECT tc.pushedClientReference as ClientReference, 
       tc.pushedStatusId as [Status], 
       tc.createdOn as DateAdded 
FROM tablebackups.dbo.tmp_colagbgdata tc
LEFT JOIN tbl_application_crb_initialData ci
ON tc.PushedClientReference = ci.ClientReference
WHERE ci.clientReference <> ''
AND tc.pushedStatusId  IN ( SELECT STATUSID FROM dbo.ref_crbStatus)

The column tc.createdOn is a datetime and it is breaking my code due to the error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Is there any way(s) I can select the record(s) that are causing this?

If I could find these records in this or similar situations, I could deal with them and re-run my query at a later time.

0

Try this query:

SELECT tc.pushedClientReference as ClientReference, tc.pushedStatusId as [Status], 
tc.createdOn 
FROM tablebackups.dbo.tmp_colagbgdata tc
where TRY_CAST(tc.createdOn as datetime) is null
and tc.createdOn is not null

The TRY_CAST function returns null when a CAST operation is impossible, so the above query will return the rows where converting the createdOn column to datetime is causing an error.

If you have a particular preference there's also TRY_CONVERT.

Now that we know the SELECT query is feeding an INSERT, I'd suggest that tc.createdon in the SELECT list be explicitly converted using TRY_CAST or TRY_CONVERT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.