469,148 Members | 1,257 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,148 developers. It's quick & easy.

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


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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200507/1
Jul 23 '05 #1
5 5556
Stu
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(smalldatetime,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

Jul 23 '05 #2
On Wed, 20 Jul 2005 16:56:42 GMT, Ray via SQLMonster.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(smalldatetime, 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)
Jul 23 '05 #3

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(smalldatetime,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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200507/1
Jul 23 '05 #4
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.

Jul 23 '05 #5

Ross,

Thank you so much ....

Ross Presser wrote:
Hi there,

[quoted text clipped - 8 lines]

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(smalldatetime, 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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200507/1
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Keith | last post: by
3 posts views Thread by Killer | last post: by
reply views Thread by larry | last post: by
2 posts views Thread by Kajsa Anka | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.