Sql命令问题 [英] Sql Command Problem

问题描述

我有一个名为accounts的表，在此表下，entryDate是一个数据类型为datetime的列。我需要一个SELECT命令，可以选择特定日期的所有数据（如'12 -02-2013'）。我用它但不行。

  SELECT  *  FROM  [accounts]  WHERE  entryDate = '  12 -02-2013' 

在此先感谢您的帮助。非常感谢您的帮助。

解决方案

在SQL数据库中，您无法直接以dd / MM / yyyy存储和检索日期值，因为在数据库中，datetime将存储为yyyy-MM-dd HH：mm：ss，



所以我建议你在sql查询中使用转换函数。

  SELECT  *  FROM  [accounts] 其中​​ entryDate = convert（ varchar （ 10 ），'  12-02-2013'， 103 ）
 
（或）
 
  SELECT  *  FROM  [accounts] 其中​​ entryDate = convert（ datetime ，'  12-02-2013'，< span class =code-digit> 103 ）

我希望您的疑问得到澄清，随时问任何问题如果你有疑问。

但不工作 - 根本没有提供信息！



取决于区域设置 [ ^ ] SQL Server可以以不同的格式存储日期数据类型：

- dd-mm-yyy

- MM / dd / yyy

- mm-dd-yyy

- 等等



要更改它，请使用设置日期格式 [ ^ ]命令，例如：

  SET   DATEFORMAT  dmy; 
  SELECT  *  FROM  [accounts]  WHERE  entryDate = '  12-02-2013' 

除上述解决方案外，SQL Server实际上以一组8字节格式存储日期时间类型，而不管语言环境设置如何。有关SQL Books on Line的详细信息，但本文涵盖大多数相关要点 [ ^ ]



我工作过的一家公司过去经常在中遇到日期问题条款，因为它是多国的。我们采用了明确的格式来克服这些问题......例如'12-FEB-2013'或'02 -DEC-2013'取决于你的意思。



我们还有一些问题，我们的日期时间字面上是日期时间 - 即包括小时，分钟，秒等，所以像 WHERE entryDate = '12 -FEB-2013'之类的东西无论如何都会失败。我们解决了这样的特殊问题...

  WHERE  dateadd（dd，datediff（dd） ， 0 ，entryDate）， 0 ）= '  12-FEB-2013' 

我们从 sqlservercentral.com上的博客 [ ^ ]

I have a table named accounts and under this table, entryDate is a column which data type is datetime. I need a SELECT command that can select all data of a particular date (like '12-02-2013' ). I use this But not work.

SELECT * FROM [accounts] WHERE entryDate='12-02-2013' 

Thanks in advance, your help is greatly appreciated.

解决方案

In SQL database you cannot store and retrieve date values directly as dd/MM/yyyy, because in database the datetime will store as yyyy-MM-dd HH:mm:ss,

So my suggestion for you to use convert function in your sql query.

SELECT * FROM [accounts] where entryDate=convert(varchar(10),'12-02-2013',103)

(or)

SELECT * FROM [accounts] where entryDate=convert(datetime,'12-02-2013',103)

I hope your doubt is cleared, feel free to ask any doubts if you have.

"But not work" - is not informative at all!

Depending on locale settings[^] SQL Server can store date data types in different formats:
- dd-mm-yyy
- MM/dd/yyy
- mm-dd-yyy
- etc.

To change it use SET DATEFORMAT[^] command, for example:

SET DATEFORMAT dmy;
SELECT * FROM [accounts] WHERE entryDate='12-02-2013'

Further to the solutions above, SQL Server actually stores datetime types in a set, 8 byte format, regardless of the locale settings. Full details on in SQL Books on Line, but this article covers most of the relevant points[^]

A company I worked for used to continuously get problems with dates in where clauses as it was multi-national. We adopted the use of unambiguous formats to overcome the issues ... e.g. '12-FEB-2013' or '02-DEC-2013' depending on which one you meant.

We also had issues where our datetime literally was a datetime - i.e. included hours, minutes, seconds etc so things like WHERE entryDate='12-FEB-2013' would fail anyway. We got around that particular problem something like this ...

WHERE dateadd(dd, datediff(dd, 0, entryDate), 0) = '12-FEB-2013'

We got the formatting stuff from this blog on sqlservercentral.com[^]

这篇关于Sql命令问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！