如何从SQL中的DATETIME列获取DATE? [英] How to get DATE from DATETIME Column in SQL?
问题描述
我在SQL Server中的表TransactionMaster中有3列
1)transaction_amount
2)Card_No
3)transaction_date-- datetime数据类型
所以,我想获取transaction_amount的SUM,其中Card_No ='123'和transaction_date =今天的日期。< -----排除时间
IN SQL
帮助我...... !!
试试这个解决方案:
创建 表 #temp
(
transaction_amount decimal ( 5 , 2 ),
Card_No nvarchar ( 50 ),
transaction_date dateTime
)
插入 进入 #temp 值( 100 。 23 ,' 123',' < span class =code-string> 2014-02-03 12:22:36.140'),
( 120 。 23 ,' 123',' 2014-02-02 12:22:36.140'),
( 140 。 23 ,' 123 ',' 2014-02-03 12:25:36.140')
SELECT SUM(transaction_amount) SUM 来自 #temp 其中 Card_No = ' 123'
AND convert ( varchar ( 12 ),transaction_date, 105 )= convert (< span class =code-keyword> varchar ( 12 ),getdate(), 105 )
drop table #temp
尝试:
CONVERT(DATE,MyDateColumn)
SQL2008,然后:
SELECT DATEADD(dd,0,DATEDIFF(dd,0,MyDateColumn))
< blockquote>在C#的帮助下,我写了以下代码,仅获取日期(todya的日期):
string date = DateTime.Now.ToString(yyyy- MM -dd);
然后将今天的日期传递给以下查询...
选择sum(transaction_amt)作为TransactionMaster的总和,其中card_no ='123'和CONVERT(varchar,trnasaction_Date,120)LIKE'+ date +%'。
还要感谢你们所有人帮助我....非常感谢.. !!
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
HELP ME...!!
Try this solution:
create table #temp ( transaction_amount decimal(5,2), Card_No nvarchar(50), transaction_date dateTime ) Insert Into #temp values(100.23,'123', '2014-02-03 12:22:36.140'), (120.23,'123', '2014-02-02 12:22:36.140'), (140.23,'123', '2014-02-03 12:25:36.140') SELECT SUM(transaction_amount) as SUM from #temp where Card_No ='123' AND convert(varchar(12), transaction_date, 105) = convert(varchar(12), getdate(), 105) drop table #temp
Try:
CONVERT(DATE, MyDateColumn)
For pre-SQL2008, then:
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, MyDateColumn))
With the help of C# i have written this below code for getting only date(todya's date):
string date= DateTime.Now.ToString("yyyy-MM-dd");
and then passed today's date to the following query...
"select sum(transaction_amt) as sum from TransactionMaster where card_no='123' and CONVERT(varchar,trnasaction_Date,120) LIKE '"+date+"%'".
Also thanks to all of you for Helping me....Thanks a LOT..!!
这篇关于如何从SQL中的DATETIME列获取DATE?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!