SQL Server 2000 - Query Analyzer : How Could I get all the transaction

[shivalisv]

SQL Server 2000 - Query Analyzer : I need to write a view where I need to extract all the transactions which took place from the begining of the month till Current Date excluding the current Date, this holds good if the user is seeing the results of the view during the mid of the month ,if the user is seeing the results at the begining of the month then all the transactions of the whole month excluding the current date should be displayed.

 

[nigelrivett]

select *
from tbl, (select dte = convert(varchar(4),dateadd(dd,-1,getdate()),112)) a
where tbl.trdte >= a.dte + '01' and tbl.trdte < dateadd(mm,1,a.dte + '01')


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[vidru]

SELECT * FROM Transactions
WHERE TransactionDate >=
CONVERT(SMALLDATETIME, CONVERT(CHAR(10), DATEADD(DAY, - DATEPART(DAY, DATEADD(DAY, -1,GETDATE())), GETDATE()), 101))
AND TransactionDate < CONVERT(SMALLDATETIME, CONVERT(CHAR(10), GETDATE(), 101))

If this is run on the first of the month, it will give the previous month's transactions.

-dave

 

[nigelrivett]

Oops - got whole month

select *
from tbl, (select dte = convert(varchar(4),dateadd(dd,-1,getdate()),112)) a
where tbl.trdte >= a.dte + '01' and tbl.trdte < convert(varchar(8),getdate(),112)

don't really need the derived table as the value is only used once so
select *
from tbl
where tbl.trdte >= convert(varchar(4),dateadd(dd,-1,getdate()),112) + '01' and tbl.trdte < convert(varchar(8),getdate(),112)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

s.b. varchar(6) not 4 to include month

Oops - got whole month

select *
from tbl, (select dte = convert(varchar(6),dateadd(dd,-1,getdate()),112)) a
where tbl.trdte >= a.dte + '01' and tbl.trdte < convert(varchar(8),getdate(),112)

don't really need the derived table as the value is only used once so
select *
from tbl
where tbl.trdte >= convert(varchar(6),dateadd(dd,-1,getdate()),112) + '01' and tbl.trdte < convert(varchar(8),getdate(),112)

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.