在查询上使用日期的问题 [英] Problem with using a date on a query

问题描述

为什么这个查询不会提取任何内容？

 选择 * 来自 FGBTRNH 
 其中 fgbtrnh_trans_date = '  30 -JUN-17' 和 fgbtrnh_rucl_code = '  JE16' 

我知道有数据的日期与该代码....但如果我写这个有效吗？是否有日期或遗漏的内容.....

 选择 * 来自 FGBTRNH 
 其中 fgbtrnh_rucl_code = '  JE16' 
 和 fgbtrnh_trans_date> = '  30-JUN-17' 

我的尝试：



我没有尝试多少但只使用更大和等号而不是仅仅等于日期

解决方案

在日期中使用此表示法：'2017-06-30'。

另请参阅： SQL Server和MySQL中的日期函数 [ ^ ]

如果您有时间组件你的日期，试试这个：

 datediff（day， date ，'  2017-06-30'）=  0  

如果您的列是DATETIME字段，那么它需要完全匹配才能使等于工作 - 直到微秒。如果你在列中的日期有一个时间组件，它就不会匹配 - 你可以通过在比较之前将其转换为DATE字段来绕过它。



但是'30 -JUN-17'可能是问题所在：它需要SQL将字符串解析为DATETIME，为此它必须对你提供的格式做出假设，这可能是错误的。

尝试与'2017-06-30'（ISO格式日期）进行比较，看看是否可以提高捕获率。

同时检查您是否正确检查了日期和代码 - SQL中的DATETIME字段没有格式，所以你可能会错误解释结果。



如果你的列包含VARCHAR或NVARCHAR值而不是日期，那么这就是你的问题，您甚至在考虑比较任何内容之前需要更改数据库。

转换您的日期时间格式。

 SELECT * FROM tablename 
 WHERE CONVERT（VARCHAR，[Datecolumn]，103）< = CONVERT（VARCHAR，@ param，103）

Hi why won't this query pull anything?

select * from FGBTRNH
where fgbtrnh_trans_date = '30-JUN-17' and fgbtrnh_rucl_code = 'JE16'

I know there is data that has that date with that code.... but if I write this it works? Is there something with the date or what am I missing.....

select * from FGBTRNH
where fgbtrnh_rucl_code = 'JE16'
and fgbtrnh_trans_date >= '30-JUN-17'

What I have tried:

I have not tried much but only using the greater and the equal sign instead of just the equal on the date

解决方案

Try this notation for date: '2017-06-30'.
Also see: Date Functions in SQL Server and MySQL[^]
If you have a time component in your date, try this:

datediff(day, date, '2017-06-30') = 0

If your column is a DATETIME field, then it would need to match exactly to get "equals" to work - right to the microsecond. If your date in the column has a time component at all, it's not going to match - you can get round that by casting it to a DATE field before comparing.

But '30-JUN-17' may be the problem: it requires SQL to parse the string to a DATETIME and for that it has to make assumptions about the format you provided it in, which may be wrong.
Try comparing with '2017-06-30' (ISO format dates) and see if that improves the catch rate.
Also check that you have checked the date and code correctly - DATETIME fields in SQL don't have a format at all, so you may be misinterpreting the result.

If you column contains VARCHAR or NVARCHAR values instead of dates, then that's your problem and you need to change your DB before you even think of comparing anything.

convert your datetime format.

SELECT * FROM tablename
WHERE  CONVERT(VARCHAR,[Datecolumn],103) <= CONVERT(VARCHAR,@param,103)

这篇关于在查询上使用日期的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！