question

LMBUX avatar image
LMBUX 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
insertdatetimedatenullconversion
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I first guess is that the LeaveDate column actually has a value in some row as 'NULL' meaning the actual string of 'NULL'. Can you check? If this is the case, you won't be able to CAST/CONVERT it into a datetime.
0 Likes 0 ·
they are empty string null [1]: /storage/temp/3819-capture.png
0 Likes 0 ·
capture.png (2.1 KiB)
@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;
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

LMBUX avatar image
LMBUX answered
This returned zero rows... source is datetime and target is datetime dateformat using DBCC USEROPTIONS is mdy for both servers
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

LMBUX avatar image
LMBUX answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

LMBUX avatar image
LMBUX answered
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!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.