i have a problem with this error i don't know why but i try many thing and search about it bout i didn't find any thing to solve this problem ( i want to make a query to calculate the age for the client auto in a column i have the Civil number for him like (286063001795) and i have some wrong civil No in my data ) the query is working good with little of data when i use it with my table gave me that error

UPDATE [dbo].[tabl]
set dob = CONVERT(date, convert(varchar(10), 17000000 + (CIVILIDD / 100000)))

select *, datediff(Month, dob, getdate())/12 as age, (CIVILIDD / 100000),17000000 + (CIVILIDD / 100000)
from [tabl]

Name_Arabic nvarchar(255) Checked CIVILIDD bigint Checked gender nvarchar(255) Checked NATIONALITY nvarchar(255) Checked dob date Checked

  • 1
    Starting with sql server 2012, there is TRY_CONVERT (also TRY_CAST, TRY_PARSE) - it tries to convert to a specific data type , and in case of error it return NULL . Add this function in your dataset and then search for NULL docs.microsoft.com/en-us/sql/t-sql/functions/…
    – Sabin B
    Jan 6 '19 at 6:25
  • @SabinBio set dob = TRY_CONVERT(date, convert(varchar(10), 17000000 + (CIVILIDD / 100000))) is that what you mean Jan 6 '19 at 6:29
  • @SabinBio how is that i have more than million record Jan 6 '19 at 6:52
  • 2
    If you have more than million records (no where clause in your update query), it's a good practice to do your update in a batch mode , see here : michaeljswart.com/2014/09/take-care-when-scripting-batches - here it's for delete ; the same could be for update
    – Sabin B
    Jan 6 '19 at 7:46
  • To identify just the bad rows use WHERE dob IS NOT NULL AND TRY_CONVERT(date, <expression>) IS NULL; Jan 6 '19 at 21:42
set dob = TRY_CONVERT(date, convert(varchar(10), 17000000 + (CIVILIDD / 100000)))
  • Hi Ahmed, can you offer an edit that shows a little more context of what's going on here to help future internet visitors? xkcd.com/979
    – jcolebrand
    Jan 8 '19 at 19:52

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.