2

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?

VS1SQL
  • 135
  • 2
  • 13

1 Answers1

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I think this may be the only option, i've just searched and i dont think SQL can store year and month only as a dateformat, this is not possible. – VS1SQL Feb 22 '18 at 10:43
  • @VS1SQL There may be a way to bulk load and transform the data. MySQL's `LOAD DATA` has this ability, and I wouldn't be surprised if SQL Server had something similar. That being said, if you want to keep the original data, then my answer is at least a decent option. – Tim Biegeleisen Feb 22 '18 at 10:48