MSSQL replace string function

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

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

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

SQL cast string to datetime

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

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