Converting data in SQL Server

Question

I am loading in a bunch of data that contains dates formatted as shown below:

00-JAN-0000
00-FEB-0000
00-MAR-0000
00-APR-0000
00-MAY-0000
00-JUN-0000
00-JUL-0000
00-AUG-0000
00-SEP-0000
00-OCT-0000
00-NOV-0000
oo-DEC-0000

SQL cannot automatically convert these dates because it does not recognize the month part of the date. I have approx 50,000 records to import. Any ideas?

OP has said in a comment:

The data I am working with are client's date of birth. Since I did not want to post someone's DOB online, I masked the day and the year with 0s but I left the month intact as that is what is causing the problem.

Answer 1

SQL can recognize the month part of the date, but it can't recognize your day (zero) or year (also zero). If you have to use BULK INSERT, try this:

• Load the data into an interim table, with the column set as char(11)
• Copy (INSERT...SELECT...) the data over to your target table

Conversion code would be something like:

INSERT MyTable (MyDate)
 select '01' + substring(@foo, 4, 3) + cast(year(getdate()) as char(4))
 from MyInterimTable

(This would set it to the first day of the current year.)

Answer 2

create a staging table where you have a YourDateString column to be populated by the data, and then an actual datetime column YourDate, which you will update in a different step. like this:

DECLARE @YourTable table(YourDateString varchar(11), YourDate datetime)

--simulate bulk load
INSERT INTO @YourTable VALUES ('01-JAN-1976',null)
INSERT INTO @YourTable VALUES ('02-FEB-1980',null)
INSERT INTO @YourTable VALUES ('05-MAR-1991',null)
INSERT INTO @YourTable VALUES ('12-APR-1997',null)
INSERT INTO @YourTable VALUES ('22-MAY-1990',null)
INSERT INTO @YourTable VALUES ('18-JUN-1971',null)
INSERT INTO @YourTable VALUES ('17-JUL-1988',null)
INSERT INTO @YourTable VALUES ('19-AUG-1993',null)
INSERT INTO @YourTable VALUES ('19-SEP-1970',null)
INSERT INTO @YourTable VALUES ('04-OCT-1982',null)
INSERT INTO @YourTable VALUES ('08-NOV-1970',null)
INSERT INTO @YourTable VALUES ('18-DEC-1983',null)

--convert to proper datetime
UPDATE @YourTable
    SET YourDate= CONVERT(datetime,YourDateString)

--show data
select * from @YourTable

OUTPUT:

YourDateString YourDate
-------------- -----------------------
01-JAN-1976    1976-01-01 00:00:00.000
02-FEB-1980    1980-02-02 00:00:00.000
05-MAR-1991    1991-03-05 00:00:00.000
12-APR-1997    1997-04-12 00:00:00.000
22-MAY-1990    1990-05-22 00:00:00.000
18-JUN-1971    1971-06-18 00:00:00.000
17-JUL-1988    1988-07-17 00:00:00.000
19-AUG-1993    1993-08-19 00:00:00.000
19-SEP-1970    1970-09-19 00:00:00.000
04-OCT-1982    1982-10-04 00:00:00.000
08-NOV-1970    1970-11-08 00:00:00.000
18-DEC-1983    1983-12-18 00:00:00.000

(12 row(s) affected)

Answer 3

How are you importing?

Are you sure one of the dates isn't a bit iffy? your (real-non-zero) date dd-mmm-yyyy format should be accepted by SQL server;

select convert(datetime, '12-NOV-1975')
1975-11-12 00:00:00.000

Answer 4

I think I discovered the problem. As I looked through the data I was sent, I see where, on some records, the previous field was null. When I split out the date then, I am only getting the last two digits of the year. That is what is throwing my error. Thanks to everyone for their help.