Sql 2008日期搜索错误 [英] Sql 2008 Date Search Error
本文介绍了Sql 2008日期搜索错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Hello Guys,
i我正在尝试搜索此查询
Hello Guys,
i am trying to search this query
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY PERS_NAME ASC) AS Row,
T_AKARAT_BILLS.*,T_AKARAT_PERS_DATA.PERS_NAME,BILL_TP_NAME
FROM T_AKARAT_BILLS join T_AKARAT_RENTS
on T_AKARAT_RENTS.ID=T_AKARAT_BILLS.RENT_ID join T_AKARAT_PERS_DATA
on T_AKARAT_PERS_DATA.ID=t_AKARAT_RENTS.TENANT_ID join T_AKARAT_BUILDINGS
on T_AKARAT_BUILDINGS.ID=T_AKARAT_RENTS.BUILDING_ID join T_AKARAT_OFFICE_DATA
on T_AKARAT_OFFICE_DATA.ID=T_AKARAT_BUILDINGS.OFFICE_ID join T_AKARAT_BILL_TP
on T_AKARAT_BILL_TP.ID=T_AKARAT_BILLS.BILL_TP_ID
where
T_AKARAT_BUILDINGS.OFFICE_ID= 3 and T_AKARAT_BILLS.BILL_DT ='23/10/2013' ) AS TableWithRowNumbers WHERE Row > '0' AND Row <= '10'
我的问题存在于(其中条件)完全是(日期值),其格式为日,月,年(2013年10月23日)
如果我使用上述查询进行搜索我收到此错误:
转换时转换失败来自字符串的日期和/或时间。
如果我将此值更改为月,日,年(2013年10月23日),则工作正常!!!!
但我需要用这种格式搜索日期(日,月,年)
任何想法?
My Problem Exists in (where Condition) exactly the (date value) which is Formatted as Day,Month,Year (23/10/2013)
If i searched with the above Query i am getting this error:
Conversion failed when converting date and/or time from character string.
If i changed this value to be Month,Day,Year (10/23/2013), it Works Fine !!!!
but i need to search date with this format (Day,Month,Year)
Any Ideas?
推荐答案
数据库列T_AKARAT_BILLS.BILL_DT应声明为DATE数据类型。
在Where子句中使用T_AKARAT_BILLS.BILL_DT时,最佳做法是使用para计量查询,其中与T_AKARAT_BILLS.BILL_DT进行比较的日期作为日期数据类型传递。
这样做,消除了日期问题!
The database column T_AKARAT_BILLS.BILL_DT should be declared as a DATE data type.
When you use T_AKARAT_BILLS.BILL_DT in the Where clause, the best practice is to use a parameterized query where the date to be compared with T_AKARAT_BILLS.BILL_DT is passed as a DATE Data Type.
Doing so, eliminates date problems!
...
SqlCommand SQLcom = new SqlCommand();
...
SQLcom.Parameters.AddWithValue("@SelectDate",dtSelectDate);
...
SQLcom.CommandText="SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY PERS_NAME ASC) AS Row,
T_AKARAT_BILLS.*,T_AKARAT_PERS_DATA.PERS_NAME,BILL_TP_NAME
FROM T_AKARAT_BILLS join T_AKARAT_RENTS
on T_AKARAT_RENTS.ID=T_AKARAT_BILLS.RENT_ID join T_AKARAT_PERS_DATA
on T_AKARAT_PERS_DATA.ID=t_AKARAT_RENTS.TENANT_ID join T_AKARAT_BUILDINGS
on T_AKARAT_BUILDINGS.ID=T_AKARAT_RENTS.BUILDING_ID join T_AKARAT_OFFICE_DATA
on T_AKARAT_OFFICE_DATA.ID=T_AKARAT_BUILDINGS.OFFICE_ID join T_AKARAT_BILL_TP
on T_AKARAT_BILL_TP.ID=T_AKARAT_BILLS.BILL_TP_ID
where
T_AKARAT_BUILDINGS.OFFICE_ID= 3 and T_AKARAT_BILLS.BILL_DT = @SelectDate) AS TableWithRowNumbers WHERE Row > '0' AND Row <= '10'";
...
我解决了我的自我,这就是答案:
T_AKARAT_BILLS.BILL_DT =转换(datetime,'23 / 10/2013',103)
I 've Solved it My self and this is the answer:
T_AKARAT_BILLS.BILL_DT = Convert(datetime,'23/10/2013',103)
这篇关于Sql 2008日期搜索错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文