SQL Server ODBC规范日期格式为DD / MM / YYYY？

Lets say I have a datetime from my database, such as: 2011-10-24 00:00:00.000. I would like to convert this into 24/10/2011, but I can't find the native SQL Server way to do this, and I'd rather no do this in the code after I run my query.

假设我的数据库中有一个日期时间,例如:2011-10-24 00:00:00.000。我想将此转换为24/10/2011,但我找不到本机SQL Server的方法来执行此操作,并且在运行查询后我不想在代码中执行此操作。

I've tried doing something as follows (as per the manual)

我尝试过如下操作(根据手册)

select CONVERT(datetime, '2011-10-24 00:00:00.000', 103);

But that doesn't work out, my result is:

但这没有用,我的结果是:

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

消息242,级别16,状态3,行1将字符数据类型转换为日期时间数据类型导致超出范围的日期时间值。

I suspect this might be due to the decimal precision, or the time part of this, but I'm not sure how to go about this without resulting to some string-hacking-regex-replace nonsense (and I'd rather no do that, I might as well end up doing the conversion after the query if I do that).

我怀疑这可能是由于小数精度,或者是时间的一部分,但是我不知道如何解决这个问题而不会导致一些字符串黑客 - 正则表达式替换废话(我宁愿不这样做) ,如果我这样做,我最终可能会在查询后完成转换)。

1 个解决方案

#1

If I read the manual correctly, you should be using stlye 120 "ODBC canonical" - no??

如果我正确阅读了手册,你应该使用stlye 120“ODBC规范” - 没有?

Try:

select CONVERT(datetime, '2011-10-24 00:00:00.000', 120);

Works on my machine :-) This gives you a SQL Server DATETIME to work with.

在我的机器上工作:-)这为您提供了一个SQL Server DATETIME。

If you want to convert that further into 24/10/2011, use a second CONVERT to convert into a VARCHAR string:

如果要将其进一步转换为24/10/2011,请使用第二个CONVERT转换为VARCHAR字符串:

select CONVERT(VARCHAR(50), CONVERT(datetime, '2011-10-24 00:00:00.000', 120), 103)

That gives me:

这给了我:

24/10/2011

#1