CODEWITHSUNDEEP
sqlsql-server
Al Phaba
Al Phaba

Reputation: 6755

How to retrieve the last day of last month in a certain format?

I know how to retrieve the last day of the last month in MS-SQL-Server 

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

This gives me the result 

2013-03-31 23:59:59.000

Now I need to change this format so that I receive it like this

mm/dd/yy

Any suggestions?

Thanks

Upvotes: 0

Views: 1997

Answers (3)

Devart
Devart

Reputation: 121902

Try this one -

DECLARE @Date DATETIME
SELECT @Date = GETDATE()

SELECT CONVERT(VARCHAR(10), DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1, 0) - 1, 101)

Output:

04/30/2013

Upvotes: 2

Jignesh.Raj
Jignesh.Raj

Reputation: 5987

-- Set date format to day/month/year.
SET DATEFORMAT dmy;
GO
DECLARE @datevar datetime2 = '31/12/2008 09:01:01.1234567';
SELECT @datevar;
GO
-- Result: 2008-12-31 09:01:01.123
SET DATEFORMAT dmy;
GO
DECLARE @datevar datetime2 = '12/31/2008 09:01:01.1234567';
SELECT @datevar;
GO
-- Result: Msg 241: Conversion failed when converting date and/or time -- from character string.

GO

Upvotes: 0

Cloudsan Chen
Cloudsan Chen

Reputation: 329

If you are using SQL server try this

SELECT CONVERT(varchar, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),101)

or 

SELECT CONVERT(varchar, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),1)

Upvotes: 2

Related Questions