How to get DATE from DATETIME Column in SQL? [duplicate]

Solution 1:

Simply cast your timestamp AS DATE, like this:

SELECT CAST(tstamp AS DATE)

SQLFiddle Demo

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.

Solution 2:

You can use

select * 
from transaction 
where (Card_No='123') and (transaction_date = convert(varchar(10),getdate(),101))

Solution 3:

Use Getdate()

 select sum(transaction_amount) from TransactionMaster
 where Card_No=' 123' and transaction_date =convert(varchar(10), getdate(), 102)

Solution 4:

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)

Solution 5:

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.