在SQL中显示日期问题 [英] Showing Date problem in SQL

问题描述

你好，

我有两个名为Pur_Mst和Pur_Trans的表，我必须将两个表连接起来才能获取一些数据.
在Pur_Trans表中，有一个名为"ExpDate"的列.该表中只有3个数据.ExpDates如12/06/2015,12/04/2013,05/08/2015.只有1个数据是2013年.虽然有条件查看2015年1月1日到2015年12月31日之间的数据，但显示的是2013年.数据.

下面是我在存储过程中写的任何内容，

选择*
来自Pur_Trans pt INNER JOIN dbo.Pur_Master pm于pm.InvoiceNo = pt.InvoiceNo
其中pt.Expdate在@Expdate和@ Expdate1之间


这是什么错误，我不知道.为什么显示2013年数据不明白.所以请大家帮忙.

在此先感谢...！

Hi there,

I have two table named Pur_Mst and Pur_Trans.I have to join both the tables to get some datas.
In Pur_Trans table there is a column named "ExpDate".There is only 3 data in that table.The ExpDates like,12/06/2015,12/04/2013,05/08/2015.Only 1 data is of 2013 yr.While am giving condition to see data between 01/01/2015 to 12/31/2015,it shows me the 2013 yr. data.

Below am mentioning whatever i have written in store procedure,

select *
from Pur_Trans pt INNER JOIN dbo.Pur_Master pm on pm.InvoiceNo=pt.InvoiceNo
where pt.Expdate between @Expdate and @Expdate1


What is error in this,I donot able to know.why it showing 2013 data donot understand.So plz anybody help me.

Thanks in advance...!

推荐答案

此类问题的最常见原因是使用字符串数据类型(例如 Varchar SQL或C#或vb中的 string 中)，而不是DateTime数据类型.

1. @Expdate和@ Expdate1是否声明为数据类型 DateTime ?
2. pt.ExpDate是否声明为DataType DateTime ?

如果不是，请将数据类型更改为 DateTime 以解决此问题.

The most common cause of this kind of issue is the use of string Data Types (like Varchar in SQL or string in C# or vb) instead of DateTime data types.

1. Are @Expdate and @Expdate1 declared as Data Type DateTime?
2. Is pt.ExpDate declared as DataType DateTime?

If not, change the Data Types to DateTime to solve this problem.

您能否确认列数据类型是datatime还是varchar?将您的数据值转换为通用数据格式并进行检查.

Can you please confirm whether the column data type is datatime or varchar?, and also please convert your data value to common data format and check it.

我知道您所追求的不是答案，但是您应该更改数据库以使用正确的日期类型并相应地更改程序.

但是要解决的问题如下 BUT ，但它很大，但是如果日期的格式或值不正确(即
)，则此操作将失败
1974年5月1日将转换为
但是
1974年2月29日会引发错误

I know its not the answer you are after but you should change your database to use the correct types for dates and alter your program accordingly.

But a work around would be as follows BUT and its a big but this will fail if the date is of an incorrect format or value i.e.

05/01/1974 would convert
but
29/02/1974 would raise an error

select *
 from Pur_Trans pt INNER JOIN dbo.Pur_Master pm on pm.InvoiceNo=pt.InvoiceNo
 where CONVERT(DATETIME, pt.Expdate) between CONVERT(DATETIME, @Expdate) and CONVERT(DATETIME, @Expdate1)

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