我在Sql Server 2005中进行查询,我需要将DateTime
变量中的值转换为格式的varchar
变量yyyy-mm-dd
(没有时间部分).我怎么做?
这是所有样式的一些测试sql.
DECLARE @now datetime SET @now = GETDATE() select convert(nvarchar(MAX), @now, 0) as output, 0 as style union select convert(nvarchar(MAX), @now, 1), 1 union select convert(nvarchar(MAX), @now, 2), 2 union select convert(nvarchar(MAX), @now, 3), 3 union select convert(nvarchar(MAX), @now, 4), 4 union select convert(nvarchar(MAX), @now, 5), 5 union select convert(nvarchar(MAX), @now, 6), 6 union select convert(nvarchar(MAX), @now, 7), 7 union select convert(nvarchar(MAX), @now, 8), 8 union select convert(nvarchar(MAX), @now, 9), 9 union select convert(nvarchar(MAX), @now, 10), 10 union select convert(nvarchar(MAX), @now, 11), 11 union select convert(nvarchar(MAX), @now, 12), 12 union select convert(nvarchar(MAX), @now, 13), 13 union select convert(nvarchar(MAX), @now, 14), 14 --15 to 19 not valid union select convert(nvarchar(MAX), @now, 20), 20 union select convert(nvarchar(MAX), @now, 21), 21 union select convert(nvarchar(MAX), @now, 22), 22 union select convert(nvarchar(MAX), @now, 23), 23 union select convert(nvarchar(MAX), @now, 24), 24 union select convert(nvarchar(MAX), @now, 25), 25 --26 to 99 not valid union select convert(nvarchar(MAX), @now, 100), 100 union select convert(nvarchar(MAX), @now, 101), 101 union select convert(nvarchar(MAX), @now, 102), 102 union select convert(nvarchar(MAX), @now, 103), 103 union select convert(nvarchar(MAX), @now, 104), 104 union select convert(nvarchar(MAX), @now, 105), 105 union select convert(nvarchar(MAX), @now, 106), 106 union select convert(nvarchar(MAX), @now, 107), 107 union select convert(nvarchar(MAX), @now, 108), 108 union select convert(nvarchar(MAX), @now, 109), 109 union select convert(nvarchar(MAX), @now, 110), 110 union select convert(nvarchar(MAX), @now, 111), 111 union select convert(nvarchar(MAX), @now, 112), 112 union select convert(nvarchar(MAX), @now, 113), 113 union select convert(nvarchar(MAX), @now, 114), 114 union select convert(nvarchar(MAX), @now, 120), 120 union select convert(nvarchar(MAX), @now, 121), 121 --122 to 125 not valid union select convert(nvarchar(MAX), @now, 126), 126 union select convert(nvarchar(MAX), @now, 127), 127 --128, 129 not valid union select convert(nvarchar(MAX), @now, 130), 130 union select convert(nvarchar(MAX), @now, 131), 131 --132 not valid order BY style
这是结果
output style Apr 28 2014 9:31AM 0 04/28/14 1 14.04.28 2 28/04/14 3 28.04.14 4 28-04-14 5 28 Apr 14 6 Apr 28, 14 7 09:31:28 8 Apr 28 2014 9:31:28:580AM 9 04-28-14 10 14/04/28 11 140428 12 28 Apr 2014 09:31:28:580 13 09:31:28:580 14 2014-04-28 09:31:28 20 2014-04-28 09:31:28.580 21 04/28/14 9:31:28 AM 22 2014-04-28 23 09:31:28 24 2014-04-28 09:31:28.580 25 Apr 28 2014 9:31AM 100 04/28/2014 101 2014.04.28 102 28/04/2014 103 28.04.2014 104 28-04-2014 105 28 Apr 2014 106 Apr 28, 2014 107 09:31:28 108 Apr 28 2014 9:31:28:580AM 109 04-28-2014 110 2014/04/28 111 20140428 112 28 Apr 2014 09:31:28:580 113 09:31:28:580 114 2014-04-28 09:31:28 120 2014-04-28 09:31:28.580 121 2014-04-28T09:31:28.580 126 2014-04-28T09:31:28.580 127 28 ????? ??????? 1435 9:31:28:580AM 130 28/06/1435 9:31:28:580AM 131
做nvarchar(max)
短修剪的时间.例如:
select convert(nvarchar(11), GETDATE(), 0) union select convert(nvarchar(max), GETDATE(), 0)
输出:
May 18 2018 May 18 2018 9:57AM
使用Microsoft Sql Server:
-- -- Create test case -- DECLARE @myDateTime DATETIME SET @myDateTime = '2008-05-03' -- -- Convert string -- SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)
请尝试以下方法:
CONVERT(varchar(10), [MyDateTimecolumn], 20)
对于完整的约会时间而不仅仅是日期:
CONVERT(varchar(23), [MyDateTimecolumn], 121)
有关转换样式,请参阅此页面:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
或
SQL Server CONVERT()函数
SQL Server 2012有一个新功能,格式:http: //msdn.microsoft.com/en-us/library/ee634924.aspx
并且您可以使用自定义日期时间格式字符串:http://msdn.microsoft.com/en-us/library/ee634398.aspx
这些页面暗示它也可以在SQL2008R2上使用,但我没有一个方便测试是否是这种情况.
用法示例(澳大利亚日期时间):
FORMAT(VALUE,'dd/MM/yyyy h:mm:ss tt')
无论是Cast
或Convert
:
语法CAST
:
CAST ( expression AS data_type [ (length ) ])
语法CONVERT
:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
http://msdn.microsoft.com/en-us/library/ms187928.aspx
实际上,因为你要求一个特定的格式:
REPLACE(CONVERT(varchar(10), Date, 102), '.', '-')
你可以用DATEPART(DATEPART, VARIABLE)
.例如:
DECLARE @DAY INT
DECLARE @MONTH INT
DECLARE @YEAR INT
DECLARE @DATE DATETIME
@DATE = GETDATE()
SELECT @DAY = DATEPART(DAY,@DATE)
SELECT @MONTH = DATEPART(MONTH,@DATE)
SELECT @YEAR = DATEPART(YEAR,@DATE)
- 这为您提供格式为'yyyy-mm-dd 00:00:00.000'的时间为0
SELECT CAST( CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME) ;
使用Microsoft SQL Server:
对CONVERT使用语法:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
例:
SELECT CONVERT(varchar,d.dateValue,1-9)
对于样式,您可以在这里找到更多信息:MSDN-Cast and Convert(Transact-SQL)。