Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (6.1k points)

I want to get the last day of a month in the below format:

"2013-05-31 00:00:00:000"

Can anyone help me out?

1 Answer

0 votes
by (12.7k points)
edited by

You can try the following code:

CREATE FUNCTION [dbo].[udf_GetLastDayOfMonth] 
(
    @Date DATETIME
)
RETURNS DATETIME
AS
BEGIN

    RETURN DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @Date) + 1, 0))

END

Query:

DECLARE @date DATETIME
SELECT @date = '2013-05-31 15:04:10.027'

SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))

Output:

-----------------------
2013-05-31 00:00:00.000

Willing to Learn SQLSign up for the SQL course by Intellipaat to clear the SQL certification exam.

Related questions

0 votes
1 answer
asked Jul 9, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.1k users

Browse Categories

...