Henry Henry - 5 months ago 15
SQL Question

Update table error with NULL and BLANK inside in SQL

I want to create update to convert datetime nvarchar to datetime. But I have problems to convert because there are NULL and BLANK.

update copy_vw_work_in_progress_so_list
Set ScanDateSO = CAST(FORMAT(cast([date_scan_so] as bigint),'####-##-## ##:##:##') as datetime)


Can you guys help me ?
I am using SQL SERVER 2014

Answer

If you know what values you want the null and blank to take on, then you could use a case expression (https://msdn.microsoft.com/en-us/library/ms181765.aspx)

Something like this:

UPDATE copy_vw_work_in_progress_so_list SET ScanDateSO = 
  CASE 
     WHEN [date_scan_so] = ' ' THEN NULL
     WHEN [date_scan_so] IS NULL THEN NULL
     ELSE 
       CAST(FORMAT(CAST([date_scan_so] AS bigint),'####-##-## ##:##:##') AS datetime)
  END