Post reply

last day of the month

  • February 26, 2002 at 7:33 am

    #78527

    How do you get last day of the month.

    Also how do I search this forum for something like this... I could not find search.

    Thanks

    Sonali

  • February 26, 2002 at 8:03 am

    #424493

    Note: I only do the convert at the end in case you are submitting a value with Date and time.

    SELECT DATEADD(mm,1,DATEADD(d,-DATEPART(dd,'1/5/2002'), CONVERT(VARCHAR,'1/5/2002',101)))

    As far as search, not 100% sure you would find what you want unless you look DATEADD AND last AND day as the criteria. Or DATEADD and dig thru the results.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • February 26, 2002 at 8:08 am

    #424494

    thanks

  • March 21, 2002 at 7:55 am

    #425815

    the above solution would work if the date format is mdy. If the date format is the default dateformat of SQL Server then the above script would fail.

    Russel Joseph


    Russel Joseph

  • March 21, 2002 at 8:00 am

    #425818

    The above script does only return the last day of the year! I think u require the last day of the month for anyt given month.

    Here lies the solotion !!! 🙂

    declare @currdate as datetime,

    @currmonth as int ,

    @nextmonth as int,

    @firstday as datetime,

    @firstdaynextmonth as datetime,

    @lastdayofmonth as int,

    @requiredate as datetime

    select @currdate = getdate()

    select 'Current date: ' + convert(varchar, @currdate)

    select @currmonth = datepart(mm, @currdate)

    select 'Current Month: ' + convert(varchar, @currmonth)

    select @nextmonth = @currmonth + 1

    select @firstday = convert(datetime, (convert(varchar, @currmonth) + '/1/' + convert(varchar, datepart(yyyy, @currdate))))

    select 'First of this month: ' + convert(varchar, @firstday )

    select @firstdaynextmonth = convert(datetime, (convert(varchar, @nextmonth) + '/1/' + convert(varchar, datepart(yyyy, @currdate))))

    select 'First of next month: ' + convert(varchar, @firstdaynextmonth )

    select @lastdayofmonth= datediff(dd, @firstday, @firstdaynextmonth)

    select @requiredate = convert(datetime, (convert(varchar, @currmonth) + '/' + convert(varchar, @lastdayofmonth ) + '/' + convert(varchar, datepart(yyyy, @currdate))))

    select @requiredate

    This is the basic script, adapt reduce the code,

    Cheers,

    Russel Joseph


    Russel Joseph

  • March 21, 2002 at 8:51 am

    #425823

    Sorry did find the mistake as I set the value to the last day of the previous month and like Feb = 28 will then rolles 3/28 for march as last this is correct.

    SELECT DATEADD(dd,-1,DATEADD(mm,1,DATEADD(d,-DATEPART(dd,GETDATE()) + 1, CONVERT(VARCHAR,GETDATE(),101))))

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • March 22, 2002 at 4:24 pm

    #425957

    its simple... find the first day of the next month and subtract a day from it. You will get the last day of that month. To find the day, use datepart.

    Paras Shah

    Evision Technologies

    Mumbai, India


    Paras Shah
    Evision Technologies
    Mumbai, India

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

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