Convert integer to date DDMMYYYY

Question

I am uploading an excel data sheet. In the sheet I have a numeric column which I want to convert to date. So 40955 should look like 04.09.1955 (DDMMYYYY) Can someone help me out here. I tried using Data Conversion transformation component and its showing me error.

PP

Answer 1

Main obstacle here is that your values are not in an easy to use format.

To do what you specify it needs to break up the value into its parts, concatenate again and then convert. All this can be done in a single statement. For explanation I show the steps below.

DECLARE 
  @someval int = 40955,
  @dateval int,
  @dated   date
;

SELECT
  -- single extraction steps
  @someval % 100 AS yearval,
  ( @someval / 100 ) % 100 AS monthval,
  ( @someval / 10000 ) AS dayval
;

SELECT 
  --@dateval = 
    -- extract year and push it to front
    ( @someval % 100 ) * 10000 
    -- extract month and push into middle
    + ( @someval / 100 ) % 100 * 100
    -- extract day and keep at end
    + ( @someval / 10000 )
;
SELECT 
  -- clip all elements into single integer
  @dateval = 
    ( @someval % 100 ) * 10000 
    + ( @someval / 100 ) % 100 * 100
    + ( @someval / 10000 )
;
SELECT
  -- 112 = yyyymmdd format
  @dated = CONVERT( date, CAST( @dateval AS varchar(8) ), 112 )
;
SELECT
  -- show as standard (format 120) date aka ISO 8601 readable
  @dated AS Dated
;

However I suspect that the value you receive from Excel is kind of Julian date. In this case the following answer will provide a solution:

convert Excel Date Serial Number to Regular Date

Keep in mind that in SSIS you need to wrap this coding into either a column or a transformation.