Firsrt day of a month

Question

Post reply

Firsrt day of a month

sahana

SSCrazy

Points: 2530
More actions
May 26, 2005 at 9:35 am

#67520

How can I get the first day of the month.
Suppose if I get a date 5/13/2005 as my input parameter I should get 5/1/2005
If I get 3/26/2005 I should get 3/1/2005.
Thanks.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

May 26, 2005 at 9:43 am

#561255

Select dateadd(M, datediff(M, 0, getdate()), 0)

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 2

declare @d datetime

set @d = '20050918'

select convert(datetime,

cast(datepart(mm, @d) as varchar) +

'/1/' +

cast(datepart(yy, @d) as varchar), 101)

Bye
Gabor

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Here's a few variations on the first/last day of month theme..

-- 2003-03-11 / Kenneth Wilhelmsson

-- This is a sample matrix showing how to find out dates of month boundries

-- from any given point in time.

set nocount on

declare @date datetime

set @date = getdate() -- the point in time from which to measure

print '''Today''s date'' is: ' + convert(char(10), @date, 121)

print ' '

-- date of the 1st of the current month

select convert(char(6), @date, 112) + '01' as '1st this month'

-- date of the last day of current month

select dateadd(day, -1, dateadd(month, 1, convert(char(6), @date, 112) + '01')) as 'last this month'

-- date of the 1st of the previous month

select dateadd(month, -1, convert(char(6), @date, 112) + '01') as '1st of last month'

-- date of the last day of the previous month

select dateadd(day, -1, convert(char(6), @date, 112) + '01') as 'last of last month'

-- date of the 1st of the next month

select dateadd(month, 1, convert(char(6), @date, 112) + '01') as '1st of next month'

-- date of the last day of the next month

select dateadd(day, -1, dateadd(month, 2, convert(char(6), @date, 112) + '01')) as 'last of next month'

set nocount off

-- alternative way of finding first day in the month of a given in-date

declare @date datetime

set @date = '1898-06-04 12:42:28.653' -- the point in time from which to measure

select DATEADD(mm, DATEDIFF(mm,0,@date), 0)

/Kenneth

ron k SSCertifiable Points: 6103 More actions · Answer 4

For lots of good information on working with sql server dates you can check out these two articles:

http://www.karaszi.com/SQLServer/info_datetime.asp

http://www.sql-server-performance.com/fk_datetime.asp

tzara Say Hey Kid Points: 663 More actions · Answer 5

my favorite...

select convert(char(7), getdate(), 120) + '-01'

or

select convert(char(6), getdate(), 112) + '01'

convert to datetimes if you need to.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

If you don't care about the time portion, you can also do:

SELECT

DATEADD(d, 1-DAY(GETDATE()), GETDATE())

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]