check if cast is possible | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

check if cast is possible

I use to import data from DBF Clipper databases into SQL Server. When a table is just imported its date fields have string format. I need to copy their data to tables of database where they ahve to be converted into date. Direct operator INSERT doesn’t convert properly (I’ve not successed in changing default date format so it’ll be covertable) but using CAST I can get result of strings like 13.05.1970 0:00:00 as datetime type. But not all records can be coverted this way. For ones can’t be converted I’ve solved to make NULL fields there. But I don’t know how to make CAST operation return NULL when convertion isn’t possible. The query
INSERT INTO people_temp
(reg_num, surname, stname, patronymic, foreing, gender, birthdate, fam_pos, dwell_type, children, nation, par_not, region, stud_fml, parn_fml,
com_prob, sp_prob, sn_passport, nn_passport, dv_passport, wg_passport)
SELECT STUDENTs_temp.REG_NOM, STUDENTs_temp.FAMILY, STUDENTs_temp.NAME, STUDENTs_temp.PARN_NAME, STUDENTs_temp.INOSTR, STUDENTs_temp.SEX,
CAST(PSPR_temp.DATA_BORN AS smalldatetime), PSPR_temp.SEM_POL, PSPR_temp.XAR_JT, PSPR_temp.CHILDREN, PSPR_temp.NATION,
PSPR_temp.SV_ROD1 + PSPR_temp.SV_ROD2 AS Expr1, PSPR_temp.REGION, PSPR_temp.STUD_FML, PSPR_temp.PARN_FML,
PSPR_temp.OB_STAJ, PSPR_temp.SP_STAJ, PSPR_temp.SN_PASPORT, PSPR_temp.NN_PASPORT, PSPR_temp.DV_PASPORT,
PSPR_temp.WG_PASPORT
FROM STUDENTs_temp INNER JOIN
PSPR_temp ON STUDENTs_temp.REG_NOM = PSPR_temp.REG_NOM
gets an error ‘The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value’. Tell me please how can make type casting return NULL if convertion isn’t possible.

Could this work? Not sure if it doesn’t give you the same error, but worth a try …<br /><br />SELECT CAST(CASE WHEN DATEADD(d, 0, PSPR_temp.DATA_BORN) IS NOT NULL THEN PSPR_temp.DATA_BORN ELSE NULL END AS SMALLDATETIME)<br />FROM PSPR_temp.DATA_BORN<br /><br />If only T-SQL had an IsDate(&lt;string&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ /> function like VBA has …
Oh silly me – there really is an ISDATE() function in T-SQL, so here it goes: SELECT CAST(CASE WHEN ISDATE(PSPR_temp.DATA_BORN) = 1 THEN PSPR_temp.DATA_BORN ELSE NULL END AS SMALLDATETIME)
FROM PSPR_temp.DATA_BORN Mind you, it doesn’t like date formats other than YYYY-MM-DD or DD-MM-YYYY, depending on the regional settings for Windows as installed on the SQL Server machine, so stick to YYYY-MM-DD. Obviously you need to check for dates before 1900 (when using smalldatetime), and you need to know that ISDATE() returns false for an otherwise correct date that is before 1753 (the minimum for datetime). You will still need some user interaction on the incorrect dates – or better have a serious talk with whoever supplies you with the Excel data – the way Excel stores data in worksheets is just hopeless.
]]>