I have to import a text file to build a table and all of the date fields currently are in integer form. It comes in three different ways. The first two are like '20191223' or '0' if there is no date. The third way in the text file is like '2170915', missing the second character. The current script that is used has the below code to import those date fields . Is there a way I can modify the import script to import the field as a date, add either a '9' or '0' to missing second character and have the '0' be blank? Thanks.
[admdat] [int] NULL,
[bthdat] [int] NULL,
You're asking for trouble here. In order for "0" to be converted to blank, the whole column is going to have to be character based. For way too many reasons to list here, that's a fabulously bad idea. The dates should be converted either to the DATETIME, or DATE datatype.
As Mike suggested, you want to load the data into a staging table (a really good idea even if the data is supposedly perfect because it's frequently/usually not) and then do your translations/modifications from there. I also suggest that your "0" date be converted to a NULL so that it actually works for DATETIME or DATE.
Seriously... storing dates as integers or text in SQL Server will do nothing but lead to an incredible world of hurt.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)