Chuyển đổi giữa String và Date trong SQL .

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the conversion from string to date (DATE, DATETIME, SMALLDATETIME).
When a conversion involves month or day name, it is language setting dependent, therefore non-deterministic. The week function is also non-deterministic because it depends on the first date setting for the week (default in sys.syslanguages table or override by SET DATEFIRST).

USE AdventureWorks

SET LANGUAGE us_english

-- Convert string to date using style (format) numbers - sql dates format

-- SQL convert text to date - SQL convert string to date / datetime

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

-- Convert datetime to text style (format) list - sql time format

-- SQL Server without century (YY) date styles (there are exceptions!)

-- Generally adding 100 to style number results in century format CCYY / YYYY

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

The BEST 70-461 SQL Server 2012 Querying Exam Prep Book!

-------------------------------------------------------

-- 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 : sqlusa