DateTime in SQL Server

Days in Month (SQL Function)

–Select dbo.Total_Days_In_Month(1,2011)

alter FUNCTION [dbo].[Total_Days_In_Month] ( @Month int,@Year Int)

RETURNS INT

AS

BEGIN

DECLARE @rtDate INT

SET @rtDate = CASE WHEN @Month IN (1, 3, 5, 7, 8, 10, 12) THEN 31

WHEN @Month IN (4, 6, 9, 11) THEN 30

ELSE CASE WHEN (@Year % 4 = 0

AND

@Year % 100 != 0)

OR

(@Year % 400 = 0)

THEN 29

ELSE 28 END

END

RETURN @rtDate

END

GO

Last Day of Previous Month

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

Last Day of Current Month

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

Select GETDATE()

Last Day of Next Month

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s