Các ví dụ sau đây sẽ giúp bạn tìm hiểu cách chuyển dữ liệu từ các kiểu DATE, DATETIME, SMALLDATETIME sang dữ liệu dạng string
— Kết quả dạng Tháng/Ngày/Năm
SELECT convert(varchar, getdate(), 101) — Ví dụ kết quả 12/30/2013
— Kết quả dạng Ngày/Tháng/Năm
SELECT convert(varchar, getdate(), 103) — Ví dụ kết quả 30/12/2013
— Kết quả dạng: Giờ:Phút:Giây
SELECT convert(varchar,getdate(), 108) — Ví dụ kết quả 09:10:34
– Kết hợp với hàm LEFT để lấy về kết quả dạng Giờ:Phút
SELECT LEFT(convert(getdate(), 108),5) – Ví dụ kết quả: 09:10
Tìm hiểu thêm các kiểu chuyển khác dưới đây
Bạn đang đọc: Chuyển kiểu DateTime sang nvarchar trong SQL
SELECT convert(datetime,’15/03/18′,3) — 2018-03-15 00:00:00.000
SELECT convert(datetime,‘15.03.18’,4) — 2018-03-15 00:00:00.000
SELECT convert(varchar,getdate()) — Mar 15 2018 10:35AM
SELECT convert(varchar,getdate(),0) — Mar 15 2018 10:35AM
SELECT convert(varchar,getdate(),1) — 03/15/18
SELECT convert(varchar,getdate(),2) — 18.03.15
SELECT convert(varchar,getdate(),3) — 15/03/18
SELECT convert(varchar,getdate(),4) — 15.03.18
SELECT convert(varchar,getdate(),5) — 15-03-18
SELECT convert(varchar,getdate(),6) — 15 Mar 18
SELECT convert(varchar,getdate(),7) — Mar 15, 18
SELECT convert(varchar,getdate(),8) — 10:39:39
SELECT convert(varchar,getdate(),9) — Mar 15 2018 10:39:48:373AM
SELECT convert(varchar,getdate(),10) — 03-15-18
SELECT convert(varchar,getdate(),11) — 18/03/15
SELECT convert(varchar,getdate(),15) — 180315
SELECT convert(varchar,getdate(),13) — 15 Mar 2018 10:41:07:590
SELECT convert(varchar,getdate(),14) — 10:41:25:903
SELECT convert(varchar,getdate(),20) — 2018-03-15 10:43:56
SELECT convert(varchar,getdate(),21) — 2018-03-15 10:44:04.950
SELECT convert(varchar,getdate(),22) — 03/15/18 10:44:50 AM
SELECT convert(varchar,getdate(),23) — 2018-03-15
SELECT convert(varchar,getdate(),24) — 10:45:45
SELECT convert(varchar,getdate(),25) — 2018-03-15 10:46:11.263
— T-SQL with century ( YYYY or CCYY ) datetime styles ( formats )
SELECT convert(varchar, getdate(), 100) — Oct 23 2016 10:22AM (or PM)
SELECT convert(varchar, getdate(), 101) — 10/23/2016
SELECT convert(varchar, getdate(), 102) — 2016.10.23
SELECT convert(varchar, getdate(), 103) — 23/10/2016
SELECT convert(varchar, getdate(), 104) — 23.10.2016
SELECT convert(varchar, getdate(), 105) — 23-10-2016
SELECT convert(varchar, getdate(), 106) — 23 Oct 2016
SELECT convert(varchar, getdate(), 107) — Oct 23, 2016
SELECT convert(varchar, getdate(), 108) — 09:10:34
SELECT convert(varchar, getdate(), 109) — Oct 23 2016 11:10:33:993AM (or PM)
SELECT convert(varchar, getdate(), 110) — 10-23-2016
SELECT convert(varchar, getdate(), 111) — 2016/10/23
SELECT convert(varchar, getdate(), 112) — 20161023
SELECT convert(varchar, getdate(), 113) — 23 Oct 2016 06:10:55:383
SELECT convert(varchar, getdate(), 114) — 06:10:55:383(24h)
SELECT convert(varchar, getdate(), 120) — 2016-10-23 06:10:55(24h)
SELECT convert(varchar, getdate(), 121) — 2016-10-23 06:10:55.383
SELECT convert(varchar, getdate(), 126) — 2016-10-23T06:10:55.383
GO
— SQL cast string to datetime – time part 0 – sql hh mm
— SQL Server cast string to DATE (SQL Server 2008 feature) – sql yyyy mm dd
SELECT [Date] = CAST(‘20120228’ AS date) — 2012-02-28
SELECT [Datetime] = CAST(‘20120228’ AS datetime) — 2012-02-28 00:00:00.000
SELECT [Datetime] = CAST(‘20120228’ AS smalldatetime) — 2012-02-28 00:00:00
— SQL convert string to datetime – time part 0
— SQL Server convert string to date – sql times format
SELECT [Datetime] = CONVERT(datetime,‘2010-02-28’)
SELECT [Datetime] = CONVERT(smalldatetime,‘2010-02-28’)
SELECT [Datetime] = CAST(‘Mar 15, 2010’ AS datetime)
SELECT [Datetime] = CAST(‘Mar 15, 2010’ AS smalldatetime)
SELECT [Datetime] = CONVERT(datetime,‘Mar 15, 2010’)
SELECT [Datetime] = CONVERT(smalldatetime,‘Mar 15, 2010’)
SELECT [Datetime] = CAST(‘Mar 15, 2010 12:07:34.444’ AS datetime)
SELECT [Datetime] = CAST(‘Mar 15, 2010 12:07:34.444’ AS smalldatetime)
SELECT [Datetime] = CONVERT(datetime,‘Mar 15, 2010 12:07:34.444’)
SELECT [Datetime] = CONVERT(smalldatetime,‘Mar 15, 2010 12:07:34.444’)
SELECT [Datetime] = CAST(‘2010-02-28 12:07:34.444’ AS datetime)
SELECT [Datetime] = CAST(‘2010-02-28 12:07:34.444’ AS smalldatetime)
SELECT [Datetime] = CONVERT(datetime,‘2010-02-28 12:07:34.444’)
SELECT [Datetime] = CONVERT(smalldatetime,‘2010-02-28 12:07:34.444’)
— Double conversion
SELECT [Datetime] = CAST(CAST(getdate() AS VARCHAR) AS datetime)
SELECT [Datetime] = CAST(CAST(getdate() AS VARCHAR) AS smalldatetime)
SELECT [Datetime] = CONVERT(datetime,convert(varchar,getdate()))
SELECT [Datetime] = CONVERT(smalldatetime,convert(varchar,getdate()))
————
— MSSQL convert date string to datetime – time is set to 00:00:00.000 or 12:00AM
PRINT CONVERT(datetime,’07-10-2016′,110) — Jul 10 2016 12:00AM
PRINT CONVERT(datetime,‘2016/07/10’,111) — Jul 10 2016 12:00AM
PRINT CONVERT(varchar,CONVERT(datetime,‘20160710’, 112),121)
— 2016-07-10 00:00:00.000
————
— Selected named date styles
DECLARE @DateTimeValue varchar(32)
— US-Style
— Convert string to datetime sql – sql convert string to datetime
SELECT @DateTimeValue = ’10/23/2016′
SELECT StringDate=@DateTimeValue,
[US-Style] = CONVERT(datetime, @DatetimeValue)
SELECT @DateTimeValue = ’10/23/2016 23:01:05′
SELECT StringDate = @DateTimeValue,
[US-Style] = CONVERT(datetime, @DatetimeValue)
— UK-Style, British/French
SELECT @DateTimeValue = ’23/10/16 23:01:05′
SELECT StringDate = @DateTimeValue,
[UK-Style] = CONVERT(datetime, @DatetimeValue, 3)
SELECT @DateTimeValue = ’23/10/2016 04:01 PM’
SELECT StringDate = @DateTimeValue,
[UK-Style] = CONVERT(datetime, @DatetimeValue, 103)
— German-Style
SELECT @DateTimeValue = ‘23.10.16 23:01:05’
SELECT StringDate = @DateTimeValue,
[German-Style] = CONVERT(datetime, @DatetimeValue, 4)
SELECT @DateTimeValue = ‘23.10.2016 04:01 PM’
SELECT StringDate = @DateTimeValue,
[German-Style] = CONVERT(datetime, @DatetimeValue, 104)
— Double conversion to US-Style 107 with century: Oct 23, 2016
SET @DateTimeValue=’10/23/16′
SELECT StringDate=@DateTimeValue,
[US-Style] = CONVERT(varchar, CONVERT(datetime, @DateTimeValue),107)
— SQL dateformat setting
USE AdventureWorks2008;
SELECT convert(datetime,’14/05/08′)
/* Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted
in an out-of-range value.
*/
SET DATEFORMAT ymd
SELECT convert(datetime,’14/05/08′) — 2014-05-08 00:00:00.000
— Setting DATEFORMAT to UK-Style
SET DATEFORMAT dmy
SELECT convert(datetime,’20/05/14′) — 2014-05-20 00:00:00.000
— Setting DATEFORMAT to US-Style
SET DATEFORMAT mdy
SELECT convert(datetime,’05/20/14′) — 2014-05-20 00:00:00.000
SELECT convert(datetime,’05/20/2014′) — 2014-05-20 00:00:00.000
GO
————
— SQL date & time eliminating dividing characters
————
— MSSQL replace string function
— T-SQL string concatenate (+)
USE AdventureWorks2008;
SELECT replace(convert(VARCHAR(10),getdate(),102),‘.’,”)
— 20120315
SELECT replace(convert(VARCHAR(10),getdate(),111),‘/’,”)
— 20120315
— SQL triple replace
SELECT replace(replace(replace(convert(VARCHAR(25),
getdate(),20),‘-‘,”), ‘:’,”),‘ ‘,”)
— 20120529090427
— T-SQL concatenating from a date and a time conversion
SELECT replace(convert(VARCHAR(10),getdate(),111),‘/’,”) +
replace(convert(VARCHAR(8),getdate(),108),‘:’,”)
— 20120315085654
————
— Converting string dates from a table
————
— Create and populate a test table with a string date
USE tempdb;
SELECT
DepartmentID,
LastUpdate=CONVERT(varchar,
dateadd(day, DepartmentID, ModifiedDate),100)
INTO DeptInfo
FROM AdventureWorks.HumanResources.Department
SELECT * FROM DeptInfo
/* Partial results
DepartmentID LastUpdate
1 Jun 2 1998 12:00AM
2 Jun 3 1998 12:00AM
*/
— Convert string date column to datetime
SELECT
DepartmentID,
LastChangeDate=convert(datetime, LastUpdate)
FROM DeptInfo
/* Partial results
DepartmentID LastChangeDate
1 1998-06-02 00:00:00.000
2 1998-06-03 00:00:00.000
*/
DROP TABLE DeptInfo
GO
——————————————————-
— Casting string date & time together and separately
——————————————————-
— SQL cast string to datetime
SELECT CAST(‘20100315 16:40:31’ AS datetime)
— Result: 2010-03-15 16:40:31.000
— SQL cast string to date – time part 0
SELECT CAST(‘20100315’ AS datetime)
— Result: 2010-03-15 00:00:00.000
— SQL cast string to time – date part 1900-01-01
SELECT CAST(’16:40:31′ AS smalldatetime)
— Result: 1900-01-01 16:41:00
————
— SQL DATEDIFF with string date
————
DECLARE @sDate varchar(10)
SET @sDate = ‘2010/03/15’
— DATEDIFF (delta) between two dates in months
SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), @sDate)
SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CAST(@sDate as datetime))
SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate))
SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate,111))
— Seme results for above: 2008-12-29 11:04:51.097 15
— SQL convert to datetime with wrong style (111 correct, 112 incorrect)
SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate,112))
/* ERROR
Msg 241, Level 16, State 1, Line 11
Conversion failed when converting date and/or time from character string.
*/
————
————
— SQL Server date string search guidelines – comparing dates
————
— Date equal search
DECLARE @Date1 datetime, @Date2 datetime, @Date3 datetime
SET @Date1 = ‘2012-01-01’
SET @Date2 = ‘2012-01-01 00:00:00.000’
SET @Date3 = ‘2012-01-01 11:00’
SELECT @Date1, @Date2, @Date3
— Date-only @Date1 is translated to datetime
— 2012-01-01 00:00:00.000 2012-01-01 00:00:00.000 2012-01-01 11:00:00.000
— The following is a datetime comparison, not a date-only comparison
IF (@Date1 = @Date2) PRINT ‘EQUAL’ ELSE PRINT ‘NOT EQUAL’
— EQUAL
— Equal test fails because time parts are different
IF (@Date1 = @Date3) PRINT ‘EQUAL’ ELSE PRINT ‘NOT EQUAL’
— NOT EQUAL
— The string date implicitly converted to datetime for the equal test
IF (‘2012-01-01’ = @Date3) PRINT ‘EQUAL’ ELSE PRINT ‘NOT EQUAL’
— NOT EQUAL
— Safe way to search for a specific date
SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
WHERE ‘2004/02/01’ = CONVERT(varchar, OrderDate,111)
— 244
— Equivalent to
SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
WHERE OrderDate BETWEEN ‘2004/02/01 00:00:00.000’ AND ‘2004/02/01 23:59:59.997’
— 244
— Safe way to search for a specific date range
SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
WHERE CONVERT(varchar, OrderDate,111) BETWEEN ‘2004/02/01’ AND ‘2004/02/14’
— 1059
— Equivalent to
SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
WHERE OrderDate BETWEEN ‘2004/02/01 00:00:00.000’ AND ‘2004/02/14 23:59:59.997’
— 1059
SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
WHERE OrderDate >= ‘2004/02/01 00:00:00.000’
AND OrderDate < ‘2004/02/15 00:00:00.000’
— 1059
————
————
— SQL Server convert from string to smalldatetime
————
— T-SQL convert from format mm/dd/yyyy to smalldatetime
SELECT CONVERT(smalldatetime, ’10/23/2016′, 101)
— 2016-10-23 00:00:00
— MSSQL convert from format dd/mm/yyyy to smalldatetime
SELECT CONVERT(smalldatetime, ’23/10/2016′, 103)
— 2016-10-23 00:00:00
— Month 23 is out of range
SELECT CONVERT(smalldatetime, ’23/10/2016′, 101)
/* Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a smalldatetime data type resulted
in an out-of-range value.
*/
————
— Translate/convert string/text hours and minutes to seconds
————
DECLARE @TimeStr varchar(16) = ’20:49:30′
SELECT PARSENAME(REPLACE(@TimeStr,‘:’,‘.’),1)
+ PARSENAME(REPLACE(@TimeStr,‘:’,‘.’),2) * 60
+ PARSENAME(REPLACE(@TimeStr,‘:’,‘.’),3) * 3600
— 74970
Source: kubet
Category: Tải Phầm Mềm
Leave a Reply