I have 3 columns in Table TransactionMaster in sql server
1) transaction_amount
2) Card_No
3) transaction_date-- datetime
datatype
So, I want to fetch SUM of transaction_amount where Card_No=' 123'
and transaction_date= todays date
.<----- excluding time IN SQL
This question is related to
sql
sql-server
date
datetime
Simply cast your timestamp AS DATE
, like this:
SELECT CAST(tstamp AS DATE)
In other words, your statement would look like this:
SELECT SUM(transaction_amount)
FROM mytable
WHERE Card_No='123'
AND CAST(transaction_date AS DATE) = target_date
What is nice about CAST
is that it works exactly the same on most SQL engines (SQL Server, PostgreSQL, MySQL), and is much easier to remember how to use it.
Methods using CONVERT()
or TO_DATE()
are specific to each SQL engine and make your code non-portable.
use the following
select sum(transaction_amount) from TransactionMaste
where Card_No = '123' and transaction_date = CONVERT(VARCHAR(10),GETDATE(),111)
or the following
select sum(transaction_amount) from TransactionMaste
where Card_No = '123' and transaction_date = CONVERT(VARCHAR(10), GETDATE(), 120)
Use Getdate()
select sum(transaction_amount) from TransactionMaster
where Card_No=' 123' and transaction_date =convert(varchar(10), getdate(), 102)
Try this:
SELECT SUM(transaction_amount) FROM TransactionMaster WHERE Card_No ='123' AND CONVERT(VARCHAR(10),GETDATE(),111)
The GETDATE()
function returns the current date and time from the SQL Server.
You can use
select *
from transaction
where (Card_No='123') and (transaction_date = convert(varchar(10),getdate(),101))
Source: Stackoverflow.com