Microsoft SQL Server forum - Code for 1st day & the Last day of the Previous Month

Author

Code for 1st day & the Last day of the Previous Month

Ray via SQLMonster.com

2005-07-20, 11:23 am

Hi there,

See if you can help me with the following:

I need to write an SQL code that will return me:

The 1st day & the Last day of the Previous Month in the following format
(smalldatetime):

yyyy-mm-dd hh:mi:ss (24h)

Regards,

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...eneral/200507/1

Stu

2005-07-20, 1:23 pm

If you don't have a calendar table (which can be very helpful for
situations like this), you can do it on-the-fly using SQL Server like
so:

SELECT DATEADD(m, -1, FirstOfCurrentMonth)
as FirstOfPreviousMonth
,
DATEADD(d, -1, FirstOfCurrentMonth)
as LastOfPreviousMonth
FROM (SELECT CONVERT(smalldatetim
e,DATENAME(m, GetDate())
+ ' 1,' + DATENAME(yyyy, GETDATE())) as FirstOfCurrentMonth)
a

Of course, you'll want to do the formatting on the client side.

HTH,
Stu

Ross Presser

2005-07-20, 1:23 pm

On Wed, 20 Jul 2005 16:56:42 GMT, Ray via webservertalk.com wrote:

> Hi there,
>
> See if you can help me with the following:
>
> I need to write an SQL code that will return me:
>
> The 1st day & the Last day of the Previous Month in the following format
> (smalldatetime):
>
> yyyy-mm-dd hh:mi:ss (24h)
>
> Regards,

declare @d1 smalldatetime
declare @d2 smalldatetime
declare @d3 smalldatetime

-- @d1 is the input date
set @d1 = CURRENT_TIMESTAMP
-- truncate hours, min, etc.
set @d1 = convert(smalldatetim
e, floor(convert(float,
@d1)))

-- @d2 - last day of previous month

set @d2 = dateadd(day, - datepart(day, @d1), @d1)

-- @d3 - first day of previous month

set @d3 = dateadd(day, - datepart(day, @d2) + 1, @d2)

Ray via SQLMonster.com

2005-07-20, 1:23 pm

Hi Stu,

Thank you very very much ..... it was a great help.

Best regards,

Stu wrote:
>If you don't have a calendar table (which can be very helpful for
>situations like this), you can do it on-the-fly using SQL Server like
>so:
>
>SELECT DATEADD(m, -1, FirstOfCurrentMonth)
as FirstOfPreviousMonth
,
> DATEADD(d, -1, FirstOfCurrentMonth)
as LastOfPreviousMonth
>FROM (SELECT CONVERT(smalldatetim
e,DATENAME(m, GetDate())
> + ' 1,' + DATENAME(yyyy, GETDATE())) as FirstOfCurrentMonth)
a
>
>Of course, you'll want to do the formatting on the client side.
>
>HTH,
>Stu

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...eneral/200507/1

--CELKO--

2005-07-20, 1:23 pm

Build a calendar table with all the temporal information you need
instead of trying to compute it on the fly. Next, the display of
temporal data has nothing to do how it is stored.

Ray via SQLMonster.com

2005-07-20, 8:24 pm

Ross,

Thank you so much ....

Ross Presser wrote:
>[quoted text clipped - 8 lines]
>
>declare @d1 smalldatetime
>declare @d2 smalldatetime
>declare @d3 smalldatetime
>
>-- @d1 is the input date
>set @d1 = CURRENT_TIMESTAMP
>-- truncate hours, min, etc.
>set @d1 = convert(smalldatetim
e, floor(convert(float,
@d1)))
>
>-- @d2 - last day of previous month
>
>set @d2 = dateadd(day, - datepart(day, @d1), @d1)
>
>-- @d3 - first day of previous month
>
>set @d3 = dateadd(day, - datepart(day, @d2) + 1, @d2)

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...eneral/200507/1

Sponsored Links