关于日期&的sql查询时间格式 [英] Sql Query Regarding Date & Time Format

问题描述

查询

"Select * from ( SELECT (convert(varchar, [InTime], 103))as Date FROM CarEnteries
Union SELECT (convert(varchar, [OutTime], 103)) as Date FROM CarEnteries)
a order by (convert(DateTime, [Date], 103)) asc"

排序
当我在一个表中合并两个日期字段的组合时，绝对工作正常，但是...
当我需要从结果中获取日期范围时，出现错误plxz帮助...
查询如下

Is working absolutly fine, when i am taking union of two date feilds in a single table but...
when i need range of date from the result i am getting error plxz help...
query is as follow

Select * from ( SELECT (convert(varchar, [InTime], 103))as Date FROM CarEnteries
Union SELECT (convert(varchar, [OutTime], 103)) as Date FROM CarEnteries)
Where (convert(DateTime, [Date], 103)) BETWEEN '08-05-2012' and '01-07-2012'
a order by (convert(DateTime, [Date], 103)) asc

以

Getting error message as

"Incorrect syntax near the keyword 'Where'."

[edit]整理出代码块-OriginalGriff [/edit]

[edit]Code blocks sorted out - OriginalGriff[/edit]

推荐答案

取出"a":

Take the "a" out:

Select * from ( SELECT (convert(varchar, [InTime], 103))as Date FROM CarEnteries
Union SELECT (convert(varchar, [OutTime], 103)) as Date FROM CarEnteries)
Where (convert(DateTime, [Date], 103)) BETWEEN '08-05-2012' and '01-07-2012'
a order by (convert(DateTime, [Date], 103)) asc

从变为

becomes

Select * from ( SELECT (convert(varchar, [InTime], 103))as Date FROM CarEnteries
Union SELECT (convert(varchar, [OutTime], 103)) as Date FROM CarEnteries)
Where (convert(DateTime, [Date], 103)) BETWEEN '08-05-2012' and '01-07-2012'
order by (convert(DateTime, [Date], 103)) asc

顺便说一句:永远不要将日期存储为字符串.而是将它们存储为DateTime，这样就不必在每次需要进行任何数学运算时都进行转换.这也使工作变得更加轻松，因为您将国际化移动到尽可能靠近输入和输出的位置-因此数据库中的数据采用完全独立的格式.

BTW: never, ever store dates as strings. Store them as DateTime instead, then you don''t have to faff with converting them every time you need to do any math. It also makes life a lot easier, because you move the internationalization to as close to input and output as possible - so the data in your DB is in a completely independent format.

您的查询语法错误.
carEnteries的末尾有一个额外的barcket.

但是，请注意，您的查询仍然无法使用(在删除此括号之后).

当您执行UNION时，在UNION 查询的所有组成部分中返回的列应返回相同的列集.

There is a syntax error with your query.
There is an extra barcket at the end of carEnteries.

However, note that your query will still not work (after removing this bracket).

When you do a UNION, the columns returned in all the constituents of the UNION queries should return the same set of columns.

如果ur [date]字段数据类型为datetime，则使用此查询将起作用

if ur [date] field datatype is datetime then use this query it will work

Select * from ( SELECT (convert(varchar, [InTime], 103))as Date FROM CarEnteries
Union SELECT (convert(varchar, [OutTime], 103)) as Date FROM CarEnteries)
Where [Date] BETWEEN '08-05-2012' and '01-07-2012'
order by [Date] asc

这篇关于关于日期&的sql查询时间格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！