-- TSQL - Date handling, convert format for input and output
Author Nigel Rivett

A few priciples
Work with dates as datetimes. This allows date arithmetic and gets round a lot of issues.

Store dates as UTC. This avoids the issue of changes due to summer time changes.
At some point you will need to calculate the time period between two dates.
If necessary also hold the source (preferred), time zone or the offset

Use an unambiguos string format for data transfer

The simplext way to deal with string character dates is to always use one of the formats
yyyymmdd			convert(varchar(8),getdate(),112)
yyyymmdd hh:mm
yyyymmdd hh:mm:ss		convert(varchar(8),getdate(),112) + ' ' + convert(varchar(8),getdate(),108)
yyyymmdd hh:mm:ss.mmm		replace(convert(varchar(24),getdate(),121)

These will be converted to datetime implicitely.
Therefore the statements
declare @s = '20160101 09:07:20'
select convert(datetime,@s)
should always work whatever the configuration of the system

An alternative is
yyyy-mm-ddThh:mm:ss.mmmm	style 126

Date formats to avoid for data transfer (including internally) are things like:

These will always be ambiguous and can eventually cause problems.

Also try to avoid the following
This can be interpreted wronly with some configration settings

This looks good and will be interpreted correctly - until your system becomes imternational and the date received is not English.

Internally it is best to work with datetimes as these will allow date arithmetic

To convert to common string data formats (note there are many other methods)

yyyymmdd			convert(varchar(8),getdate(),112)
yyyymmdd hh:mm:ss		convert(varchar(8),getdate(),112) + ' ' + convert(varchar(8),getdate(),108)
yyyymmdd hh:mm:ss.mmm		replace(convert(varchar(24),getdate(),121)
dd/mm/yyyy			convert(varchar(10),getdate(),113)
dd/mm/yyyy hh:mm:ss		convert(varchar(10),getdate(),113) + ' ' + convert(varchar(8),getdate(),108)
dd/mm/yyyy hh:mm:ss.mmm		convert(varchar(10),getdate(),113) + ' ' + right(convert(varchar(24),getdate(),121),12)
dd-mm-yyyy			convert(varchar(10),getdate(),105)
dd mmm yyyy			convert(varchar(11),getdate(),106)
yyyy-mm-ddThh:mm:ss.mmm		convert(varchar(23),getdate(),126)
mm/dd/yyyy			convert(varchar(10),getdate(),101)
mm/dd/yyyy hh:mm:ss		convert(varchar(10),getdate(),101) + ' ' + convert(varchar(8),getdate(),108)

I only tend to remeber style 112, 108, 121, 126
I use 126 for logging reports where I need to present milliseconds and want something simple.

The convert function allows a style as the third parameter.
This is used for converting both to and from dates so
declare @d datetime = getdate()
declare @s varchar(100)
select @s = convert(varchar(10),@d,113) + ' ' + convert(varchar(8),@d,108)	-- dd/mm/yyyy hh:mm:ss
select convert(datetime,@s,113)

Date arithmetic works with dates. A date is held in two parts with the integer part being the number of days from 19000101
An expression contains a date then all items will usually be implicitely converted to a datetime

datetime @d
Add 1 day
@d = @d + 1
@d = dateadd(dd,1,@d)
@d = @d + '19000102'

add one hour
@d = @d + '01:00:00'
@d = @d + '19000101 01:00:00'
@d = dateadd(hh,1,@d)

Convert a datetime to a date @d
@d = convert(varchar(8),getdate(),112)
	This has the advantage that it is clear what is happening but returns a string which then needs to be converted
	It is not very efficient so only use it to convert single or a few date but not on large tables
@d = dateadd(dd,datediff(dd,getdate(),0)
	This returns a date but can be confusing in a complicated expression.