asked
issue inserting NULL field in datetime column
I am having an issue with this code below. It give me this error: Conversion failed when converting date and/or time from character string. The datevalue is either actually NULL (not text NULL) and the date is in the format 2016-12-02. Anyone know how to get round this, have tried using cast and convert into datetime, this hasnt worked.... select PersonalID ,[LeaveDate] as [LeaveDate] into #temp1 from (SELECT [staffid] as PersonalID ,[leaving_d] as [LeaveDate] FROM rcmsql4.[SSTRESS].[dbo].[vwGetStaffDetails] UNION ALL SELECT [StudentId] as PersonalID , NULL FROM rcmsql4.[SSTRESS].[dbo].[vwGetResearchStudentDetails]) x GROUP BY PersonalID ,[LeaveDate] INSERT INTO [dbo].[STG1StaffProfiles] ([PersonalID] ,[LeaveDate] --datetime field) select * from #temp1
@LMBUX What is the actual data type of the column named **leaving\_d** of the **rcmsql4.[SSTRESS].[dbo].[vwGetStaffDetails]** table? Is it possible that the data type is character based? If so, and the version of SQL Server is 2012 or better then you can try to run this query which will reveal whether there are any NOT NULL values in **leaving\_d** which cannot be converted to datetime:
select
staffid, leaving_d from rcmsql4.[SSTRESS].[dbo].[vwGetStaffDetails]
where leaving_d is not null and try_cast(leaving_d as datetime) is null
order by staffid;
For earlier versions, you can check using isdate instead of try\_cast:
select
staffid, leaving_d from rcmsql4.[SSTRESS].[dbo].[vwGetStaffDetails]
where leaving_d is not null and isdate(leaving_d) = 0
order by staffid;
Have you checked that all other columns have the same size/length? Check that with select c.name, c.system_type_id, c.max_length from rcmsql4.SSTRESS.sys.views v inner join sys.columns c on v.object_id = c.object_id where v.name = 'vwGetStaffDetails' and v.schema_id=1 order by c.column_id AND select c.name, c.system_type_id, c.max_length from sys.views v inner join sys.columns c on v.object_id = c.object_id where v.name = 'STG1StaffProfiles' and v.schema_id=1 order by c.column_id
Like @oleg says, checking with ISDATE is a good idea, to see which rows can't be converted to null. You might also want to check which dateformat you have in your database Connection. Check that using DBCC USEROPTIONS. I have 'mdy' in my current Connection.
This returned zero rows... source is datetime and target is datetime dateformat using DBCC USEROPTIONS is mdy for both servers
When I do this: INSERT INTO [dbo].[STG1StaffProfiles] ([PersonalID] ,[title] ,[username] ,[forename] ,[preferred_nm] ,[surname] ,[department] ,[job_title] ,[email] ,[Phone] ,[LeaveDate] ,[Staff]) SELECT [staffid] as PersonalID ,[title] ,[username] ,[forename] ,[preferred_nm] ,[surname] ,[department] ,[job_title] ,case when [email] like
'%%@rcm.ac.uk' then [email] else null end as email ,[telephonenumber] ,[leaving_d] as [LeaveDate] ,1 as Staff FROM rcmsql4.[SSTRESS].[dbo].[vwGetStaffDetails] where [Leaving_d] is null or leaving_d>getdate() Msg 8152, Level 16, State 14, Line 4 String or binary data would be truncated. The statement has been terminated.
Ok so the issue was that I nested the union all within the brackets and had a select around the outside of it, for some reason this was causing it to error. Love the above query to Magnus, this helped too! All fixed, thank you all!
19 People are following this question.
