June 28, 2003 at 2:47 am
how can i select from start day of the previous month AND last day of the previous month
but always from GETDATE()
-------------------
SELECT id AS Expr1, Fname, fld2, fld1 AS Expr2, fld4
FROM dbo.Sn
WHERE (fld1 BETWEEN DATEPART(mm, GETDATE()) AND DATEPART(mm, GETDATE())) AND (fld2 = 12)
---------------------------
thnks
ilan
Edited by - midan1 on 06/28/2003 03:42:29 AM
June 28, 2003 at 10:51 am
i think i found the answer
----------
SELECT id, Fname, fld2, fld1
FROM dbo.Sn
WHERE (fld1 BETWEEN DATEADD([day], - DAY(GETDATE()) + 1, GETDATE()) AND DATEADD(m, 1, GETDATE() - (DAY(GETDATE()) - 1)) - 1) AND (fld2 = 12)
-----------------------
ilan
June 30, 2003 at 6:51 am
Personally I would do the following
declare @fromdate datetime
declare @todate datetime
set @fromdate = CAST(CONVERT(varchar(8),getdate(),120)+'01' AS datetime)
set @todate = DATEADD(second,-1,@fromdate)
set @fromdate = DATEADD(month,-1,@fromdate)
SELECT id, Fname, fld2, fld1
FROM dbo.Sn
WHERE (fld1 BETWEEN @fromdate AND @todate) - 1) AND (fld2 = 12)
It caters for dates with times and easily readable for the next person.
Far away is close at hand in the images of elsewhere.
Anon.
June 30, 2003 at 2:50 pm
hi
i get this
-------------
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '-'.
----------------------------------------
from this line
WHERE (fld1 BETWEEN @fromdate AND @todate) - 1) AND (fld2 = 12)
-----------------------------------------
thnks ilan
June 30, 2003 at 3:41 pm
DavidBurrows, to split hairs it may be safer to subtract 3 milliseconds, depending on the provider of the data. Run into such a funny during development, recently. Otherwise I am using the same code in a production environment.
Declare @FromDate datetime
Declare @ToDate datetime
Set @FromDate = CAST(CONVERT(varchar(8),getdate(),120)+'01' AS datetime)
Set @ToDate = DATEADD(ms,-3,@FromDate)
Set @FromDate = DATEADD(month,-1,@FromDate)
Select @FromDate,@ToDate
July 1, 2003 at 2:16 am
Sorry ilan,
The line should be
WHERE (fld1 BETWEEN @fromdate AND @todate) AND (fld2 = 12)
Len,
Your right, good idea. Never too old to learn.
Far away is close at hand in the images of elsewhere.
Anon.
July 1, 2003 at 2:31 am
Len,
In addition I only did it this way due to the use of BETWEEN. I don't use BETWEEN personally, I always code the above as follows
declare @fromdate varchar(10)
declare @todate varchar(10)
set @fromdate = CONVERT(varchar(8),DATEADD(month,-1,GETDATE()),120)+'01'
set @todate = CONVERT(varchar(8),GETDATE(),120)+'01'
SELECT id, Fname, fld2, fld1
FROM dbo.Sn
WHERE fld1 >= @fromdate
AND fld1 < @todate
AND fld2 = 12
Far away is close at hand in the images of elsewhere.
Anon.
July 2, 2003 at 1:16 pm
ahhhhhhhhhhhhh
evry day i learn something new
thnks a lot
ilan
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply