Last day of the previous month - Format MM/DD/YYYY

Question

Post reply

Last day of the previous month - Format MM/DD/YYYY

SQL Server

Old Hand

Points: 377
More actions
April 14, 2021 at 6:34 pm

#3871204
I use the below to get Last day of Previous Month, but I want the date format to be MM/DD/YYYY. We are sending that in the output file.
```
REPLACE(LEFT((DT_WSTR,20)DATEADD("d",-1,DATEADD("m",DATEDIFF("m",(DT_DATE)"1901-01-01",GETDATE()),(DT_DATE)"1901-01-01")),10),"-","/")
```
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 1

Lookup the key word CONVERT. You'll need to use the format number of 101.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Server Old Hand Points: 377 More actions · Answer 2

You mean like this? This is throwing error. I thought the convert 101 is for T-SQL.

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

This reply was modified 3 years, 8 months ago by SQL Server.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 3

look at this http://bilearninghub.blogspot.com/2018/05/format-date-in-ssis-mm-dd-yyyy-or.html

RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +"-"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)+"-"+(DT_STR,4,1252)DATEPART( "yyyy" , getdate() )

as you are doing a bit of calculation with the dates I would advise that that is done on an earlier step and then use that variable/column on the final formatting formula.

This reply was modified 3 years, 8 months ago by frederico_fonseca.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

As Jeff mentioned, CONVERT + 101 gets you the formatting you need:

SELECT CONVERT(VARCHAR(10), GETDATE(), 101);

For the last day of the previous month:

SELECT EOMONTH(DATEADD(MONTH,-1,GETDATE()));

For the last day of the previous month formatted as needed:

SELECT CONVERT(VARCHAR(10), EOMONTH(DATEADD(MONTH,-1,GETDATE())), 101);

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001