December 22, 2007 at 9:08 am
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
December 22, 2007 at 12:04 pm
midan1 (12/22/2007)
how to findday 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.
December 22, 2007 at 1:02 pm
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
December 22, 2007 at 1:11 pm
Assuming that the date_fld has a datatype of datetime, are there varying times in the field, or are they all 00:00:000?
December 22, 2007 at 1:27 pm
midan1 (12/22/2007)
hii 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
December 22, 2007 at 1:43 pm
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
December 22, 2007 at 1:54 pm
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)
December 22, 2007 at 1:58 pm
December 22, 2007 at 2:31 pm
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
December 22, 2007 at 8:18 pm
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
Change is inevitable... Change for the better is not.
December 22, 2007 at 11:36 pm
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.
December 22, 2007 at 11:54 pm
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
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply