About the first Day and the last Day

How should I do to get the first day and the last for a given month such as '200310' or '200309' and so on ? Please help me !select dateadd(dd,-1*datepart(dd,@.d)-1,@.d)
select dateadd(mm,1,dateadd(dd,-1*datepart(dd,@.d)-1,@.d))|||it is not working!!!!!!!!!!!
I tried it

cyrus|||Can u specify ur question properly??
First day for any month is always 1 and tnow the question is to find a last day of a month that can be done!!!!!
but can u tell what u want

Cyrus|||If I past a string '200309',it should return the '20030901' and '20030930' to me. My question is something like the description above. The starting string for a month can be variable. Is all above clear to describe my question ?|||This two statement will solve ur problem

for last day
select dateadd(mm,1,dateadd(dd,-1,'200310'+'01'))

for first day

select '200310'+'01'

if u require explanation tell me|||Thanks a lot, Cyrus! I will try the statements you just show me. I have just resolved my problem to use a While loop. I left the procedure on my computer, I will show you the statements tomorrow. Thank you again for your help.|||Originally posted by cyrus
it is not working!!!!!!!!!!!
I tried it

cyrus

DECLARE @.d int
SELECT @.d = 1

select dateadd(dd,-1*datepart(dd,@.d)-1,@.d)
select dateadd(mm,1,dateadd(dd,-1*datepart(dd,@.d)-1,@.d))

You need to supply the local variable

Now try it...(and please cut and paste)|||Mr. Brett Kaiser
thanks for ur advice.
I have executed the query successfully without u help and then reached a conclusion that the query was not working right.

Thanks for ur unneedful advice

Cyrus|||I am sorry to reply so late. The statements I wrote yesterday as below:

declare @.startday char(8)
declare @.startdate datetime
declare @.enddate datetime
DECLARE @.DAILY CHAR(2)
declare @.today datetime
DECLARE @.NEXTDAY DATETIME
-- @.ym is the variable of Year_Month in yyyymm
set @.startday = @.ym + '01'
set @.startdate = convert(datetime,@.startday)

-- Is there any month not starting with '01' ?
set @.startdate = convert(char(10),@.startdate,121) + ' 07:00:00'

set @.today = @.startdate

while month(@.today) <= month(@.startdate)
begin
set @.today = @.today + 1
end

set @.enddate = convert(char(10),@.today,121) + ' 07:00:00'

Select date_mark from history where some_date >= @.startdate and some_date < @.enddate

Is there any advice from you ? Thanks again, and also thanks to Kaiser.|||What do u want to do mosu ?
Is ur query trying to find records between start date and end date?

Cyrus|||Hi,Cyrus,
What you think is exactly correct. I try to create a report what's duration is one month given by user. It is unknown what month will be selected until user selects one.
By the way, I had tried your method yesterday. The result is as follow.

select dateadd(mm,1,dateadd(dd,-1,'200310'+'01'))

the result it returned is '2003-10-30 00:00:00.000' !

It will not give me the last day of October, I think it should be '2003-10-31 00:00:00'. But I still thank you for your patience to try out my problem so enthusiastically.|||the reason you got october 30 is because that formula is flawed

it started with '200310'+'01', then subtracted a day, then added a month

what you should to do get the last day of the current month is start with '200310'+'01', then add a month, then subtract a day

easy, innit

rudy|||its this mosu.

select dateadd(dd,-1,dateadd(mm,1,'200302'+'01'))

hope this is right|||You can put a condition as follow
But here take care that month is always two digit so concat '0' when it is singel digit

where ltrim(str(year(startdate))) + ltrim(str(month(startdate))) ='200203'|||Thanks again,Cyrus. And also thanks to Rudy.Both you resolve my confuse. I was too fool to read the statement. Without Rudy's explanation, I got nothing from Cyrus.I hope I can learn so much as you two.
Now I got another question that makes me headache! I will post it later and hope both you would help me again.