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.