Bring correct date format in SSIS

Question

I need date format in American Format i.e. 9/30/2018; 8/31/2018; 7/31/2018.. so on and so forth in SSIS. I have written the code in the format as

LEFT((DT_STR,50,1252)DATEADD("d",-DAY(GETDATE()),GETDATE()),10)

This is bringing date in 2018-09-30 which is not the proper format. I do have given the data type as "STRING" as the above code doesn't take "DATE/DATE-TIME" as data type.

I am trying to bring the previous month last date and hence the format currently being fetched is not right.

Any guesses?

Thanks!

maybe this is a good read http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes — GuidoG, Oct 23 '18 at 14:10

score 2 · Accepted Answer · answered Oct 23 '18 at 14:37

For a format like this, the date parts will need to be extracted from the date and concatenated accordingly. The expression below will convert the date to the DD/MM/YYYY format. Since you only listed single digits for the month in your question, this example does not account for zeros and the length will vary. If you want zeros added to single digit days and months, a "0" (with quotes) will need to be appended before the day and month.

RIGHT((DT_STR, 2, 1252) DATEPART("MM", DATEADD("D",-DAY(GETDATE()),GETDATE())), 2) 
+ "/" + RIGHT((DT_STR, 2, 1252) DATEPART("DD", DATEADD("D",-DAY(GETDATE()),GETDATE())), 2) 
+ "/" +  (DT_STR, 4, 1252) DATEPART("YYYY", DATEADD("D",-DAY(GETDATE()),GETDATE()))

Ilyes · Answer 2 · 2018-10-23T18:19:27.863

How about that

DECLARE @AsStr  VARCHAR(10) = '2018-09-30', --If you store it as string
        @AsDate DATE = '2018-09-30'; --If you store it as date

SELECT CONVERT(VARCHAR(10), @AsDate, 101) AsStr,
       CONVERT(VARCHAR(10), CAST(@AsStr AS DATE), 101)  AsDate;

Returns

+------------+------------+
|   AsStr    |   AsDate   |
+------------+------------+
| 09/30/2018 | 09/30/2018 |
+------------+------------+

Or you can use FORMAT() function as

SELECT
       FORMAT(CAST(@AsStr AS DATE), 'MM/dd/yyyy') FormatStr,
       FORMAT(@AsDate, 'MM/dd/yyyy') FormatDate;

Returns

+------------+------------+
| FormatStr  | FormatDate |
+------------+------------+
| 09/30/2018 | 09/30/2018 |
+------------+------------+

score 0 · Answer 3 · answered Oct 23 '18 at 18:32

You can use DATEFROMPARTS to get the first day of the month fairly easily. Then, you can use DATEADD to subtract a day, then CONVERT to output the 101 format which is in the form MM/DD/YYYY.

For example:

DECLARE @DT_STR Date = '2018-10-23'
SELECT CONVERT(varchar, DATEADD(DAY, -1, DATEFROMPARTS(YEAR(@DT_STR), MONTH(@DT_STR), 1)), 101) AS [answer]

Produces output:

answer
09/30/2018

Bring correct date format in SSIS

3 Answers3