SQL date styles and SET Date format in SQL

 USE AdventureWorks

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

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