希望日期格式为mm / dd / yyyy - 10/02/2008 [英] want date format as mm/dd/yyyy – 10/02/2008

问题描述

我有下面的SQL查询



SELECT转换（nvarchar，dtBirthDate，103）为'出生日期'

FROM tblEmployee



它给出结果2015年12月29日12:00 AM

i have sql query below

SELECT convert(nvarchar, dtBirthDate, 103) as 'Date Of Birth'
FROM tblEmployee

it give result "Dec 29 2015 12:00AM"

推荐答案

SQL CONVERT函数在格式之间转换 - 并且DATETIME说明符103确实生成了mm / dd / yyyy。

不幸的是，它仅在您为其提供DATETIME值以转换为VARCHAR或NVARCHAR值时才有效 - 所以如果你是从您的SELECT获取Dec 29 2015 12:00 AM，那是因为您已经将日期存储在VARCHAR或NVARCHAR字段中，并且SQL不会从基于字符串的字段转换为基于字符串的字段。

亲自尝试：

The SQL CONVERT function converts between formats - and the DATETIME specifier 103 does indeed generate "mm/dd/yyyy".
Unfortunately, it only works when you feed it a DATETIME value to convert to a VARCHAR or NVARCHAR value - so if you are getting "Dec 29 2015 12:00AM" from your SELECT, then that's because you are storing your dates in VARCHAR or NVARCHAR fields already, and SQL won't convert from a string based field to a string based field.
Try it yourself:

SELECT convert(nvarchar, GETDATE(), 103) as 'Date Of Birth1',
       convert(nvarchar, GETDATE())as 'Date of Birth2',
       convert(nvarchar, convert(nvarchar, GETDATE()), 103) as 'Date Of Birth3'

始终将值存储为适当的数据类型：以后其他任何内容都会给您带来问题。

更改你的数据库设计现在，在它成为一个需要修复的问题之前 - 或者在某些时候，无效的日期值会进入那里，并且它将成为一个真正的问题来整理！

Always store values as the appropriate datatype: anything else will give you problems later.
Change your database design now, before it does become a problem to fix - or at some point an invalid date value will get in there, and it will get to be a real problem to sort out!

˚F ormat 103产生所需的输出，所以最有可能的话，如果值没有正确显示，问题出在其他地方。



尝试使用

Format 103 yields to the desired output so most likely if the value isn't shown correctly the problem lies elsewhere.

Have a try with

SELECT convert(nvarchar, GETDATE(), 103) as 'Date Of Birth'

结果是

the result is

Date Of Birth
09/01/2016

但是，应该定义nvarchar的长度，因为没有指定长度换句话说你应该更喜欢

However, the length for the nvarchar should be defined since without specifying the length it would be 1. In other words you should prefer

SELECT convert(nvarchar(10), GETDATE(), 103) as 'Date Of Birth'

这篇关于希望日期格式为mm / dd / yyyy - 10/02/2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！