how to find - day before Last day of the month

  • how to find

    day before Last day of the Previous Month

    i need to find the value in my table

    "day before Last day of the Previous Month"

    TNX

  • midan1 (12/22/2007)


    how to find

    day before Last day of the month

    i need to find the value in my table

    "day before Last day of the month"

    TNX

    I have a strong feeling we're missing a lot of info here, and would like clarification before anyone takes the time to whip this out.

    While at first glance, it looks like you want code that returns the second to the last day of the current month, I'm pretty sure that's not really your goal. Additionally, what do you mean when you say you need to find the value in your table?

    How about starting with the SQL you have so far, as that might start us down the right path.

  • hi

    i ask the friends here if thay can help only !

    i am working on complex project and i need it

    to find the "day before Last day of the Previous Month"

    --Last Day of Previous Month

    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0)) LastDay_PreviousMonth

    --Last Day of Current Month

    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) LastDay_CurrentMonth

    --Last Day of Next Month

    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+2,0)) LastDay_NextMonth

    ---------------------------------------------

    how to do

    select * from tb1

    where

    date_fld= (day before Last day of the Previous Month)

    TNX

  • Assuming that the date_fld has a datatype of datetime, are there varying times in the field, or are they all 00:00:000?

  • midan1 (12/22/2007)


    hi

    i ask the friends here if thay can help only !

    i am working on complex project and i need it

    to find the "day before Last day of the Previous Month"

    --Last Day of Previous Month

    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0)) LastDay_PreviousMonth

    --Last Day of Current Month

    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) LastDay_CurrentMonth

    --Last Day of Next Month

    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+2,0)) LastDay_NextMonth

    ---------------------------------------------

    how to do

    select * from tb1

    where

    date_fld= (day before Last day of the Previous Month)

    TNX

    DECLARE @myDate datetime;

    SET @myDate = DATEADD(day, -2, DATEADD(month, DATEDIFF(month, 0, getdate()), 0));

    SELECTcolumn list

    FROMtbl1

    WHEREdate_fld >= @myDate

    ANDdate_fld < dateadd(day, 1, @myDate)

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • can i use it inside a view ?

    this is my table

    my table

    ----------------------

    empid basedate unit_date shift

    ----------------------------------------------------

    28895472 28/04/2007 28/01/2007 1

    56496581 28/04/2007 30/01/2007 3

    56157795 28/04/2007 11/01/2007 5

    51679900 28/04/2007 12/01/2007 4

    54693130 28/04/2007 10/01/2007 4

    i need to search in the filed "basedate"

    TNX

    TNX for all

  • You still didn't tell us if there is a varying time value in the date_fld column.

    If there is, I'd do this:

    SELECT

    *

    FROM

    tb1

    WHERE

    date_fld >= DATEADD(d,-2,DATEADD(mm, DATEDIFF(m,0,getdate()),0))

    ANDdate_fld < DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))

    and if not, do this:

    SELECT

    *

    FROM

    tb1

    WHERE

    date_fld = DATEADD(d,-2,DATEADD(mm, DATEDIFF(m,0,getdate()),0)

  • I'd also highly recommend reading this article[/url].

  • TNX it help

    it working 100%

    can i do this is it right ?

    i need to know

    i must to search between 2 rows

    1 )day before Last day of the Previous Month

    2)"day before Last day of the Previous Month"

    for each employee

    --------------------

    SELECT

    [new_shift] =

    CASE

    WHEN (empid IN

    (SELECT empid

    FROM empbase

    WHERE (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5) and

    (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)))

    THEN 1

    WHEN

    (empid IN

    (SELECT empid

    FROM empbase

    WHERE (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1) and

    (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)))

    THEN 2

    ELSE 99

    END ,

    SilokE.empid, SilokE.Fname, SilokE.basedate, mhlkot.mhlka

    FROM SilokE INNER JOIN

    mhlkot ON SilokE.mhlka = mhlkot.mhlka_id

    tnx for all wonderful people

  • I have to admit, I'm confused as to why the question was even asked... the OP had a "Last day of the Month" formula... surely it's an easy deduction to say "I need to subtract 1 day from that." 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (12/22/2007)


    I have to admit, I'm confused as to why the question was even asked... the OP had a "Last day of the Month" formula... surely it's an easy deduction to say "I need to subtract 1 day from that." 😛

    That's why I pointed the OP to your article.

    As an aside, the formula that he was given wasn't a perfect one, as it returned the date with a time of 11:59:59.000, so it wouldn't have matched a date with 00:00:00.000 using equals, and it would have missed everything between 11:59:59.003 and 11:19:59.997 if testing for a range. That's why I was trying to get the OP to tell us if the date column had varying time values.

  • Crud... I just glanced at the formulas... didn't see that they were subtracting 1 second.

    Also, didn't know which artical you were referring to... it doesn't show in the post.

    Thanks for the feedback, David.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

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