How to format year and month only, from a bulk insert

Question

I have data from excel that has only the year and month e.g 2012-01

With 01 being January.

For my Script:

IF OBJECT_ID('[test].[dbo].[yearMONTH]', 'U') IS NOT NULL
   begin
      DROP TABLE [test].[dbo].yearMONTH;
      print 'Dropped Table [test].[dbo].[yearMONTH]';
   end
GO


CREATE TABLE [test].[dbo].[yearMONTH]
    (
    [yearMONTH]     date
    );
GO

BULK INSERT [test] FROM 'C:\Users\....csv'
With 
   (
   FirstROW = 2,
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
   );

I know that this will fail as the SQL won't recognise the data's date format.

I was thinking to input it as a string? but that still won't solve the problem for storing it as year month date in SQL.

Would I have to bulk insert the data as varchar? then transform the datatype to date with each [year-month] having 01 at the end for day, e.g. 2012-01-01, then cut off the day and place year-month in a separate column?

The only thing i see with that solution is the new [year - month] date wont be stored as a date format once i separate the year and month from day?


I suppose the first step is to import the data from excel into sql and then transform the string 'yyyy-mm' into a date format 'yyyy-mm-dd' but i'm not sure how to do this?

Answer 1

One brute force option would be to just bulk insert your partial date 2018-02 data as text, and then build a date column afterwards:

ALTER TABLE test ADD your_date DATETIME;

UPDATE test
SET your_date = CONVERT(date, orig_date + '-01');

Then, you may drop the original text date column if you no longer need it:

ALTER TABLE test DROP COLUMN orig_date;

This of course assumes that you are OK to set each date to the first of the month.