question

SQL-4608 avatar image
0 Votes"
SQL-4608 asked YitzhakKhabinsky-0887 edited

SSIS - Previous End of Month Format MM/DD/YYYY

I use the below to get Last day of Previous Month, but I want the data format to be MM/DD/YYYY. The below displays in YYYY/MM/DD.

REPLACE(LEFT((DT_WSTR,20)DATEADD("d",-1,DATEADD("m",DATEDIFF("m",(DT_DATE)"1901-01-01",GETDATE()),(DT_DATE)"1901-01-01")),10),"-","/")

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @SQL-4608,

SSIS deals with raw data.

  • Raw data layer (database, files, SSIS, etc.), Date always shall be in the yyyy-MM-dd format.

  • Presentation layer is responsible for raw DATE formatting.

The yyyy-MM-dd format is based on the ISO 8601 standard: iso-8601-date-and-time-format


UPDATE
Please try the following expression:

 TOKEN(LEFT(
  (DT_WSTR,20)DATEADD("d",-1,DATEADD("m",DATEDIFF("m",(DT_DATE)"1901-01-01",GETDATE()),(DT_DATE)"1901-01-01"))
 ,10),"-",2) + "/" +
 TOKEN(LEFT(
  (DT_WSTR,20)DATEADD("d",-1,DATEADD("m",DATEDIFF("m",(DT_DATE)"1901-01-01",GETDATE()),(DT_DATE)"1901-01-01"))
 ,10),"-",3) + "/" +
 TOKEN(LEFT(
  (DT_WSTR,20)DATEADD("d",-1,DATEADD("m",DATEDIFF("m",(DT_DATE)"1901-01-01",GETDATE()),(DT_DATE)"1901-01-01"))
 ,10),"-",1)

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


The Output file that we are sending we want the format to be MM/DD/YYYY. Is this possible?

0 Votes 0 ·

@SQL-4608,

I updated my answer. Please check its UPDATE section.

0 Votes 0 ·