MYSQL Date Formatting

I always feel like I'm digging around to find a list of date formats for MYSQL.  I then end up doing some trial and error to get the desired format. 

Below is helpful list to refer to for common date formatting.

Example	Results
SELECT NOW()	2020-02-23 19:45:14
SELECT DATE_FORMAT(NOW(),"%a")	Sun
SELECT DATE_FORMAT(NOW(),"%b")	Feb
SELECT DATE_FORMAT(NOW(),"%c")	2
SELECT DATE_FORMAT(NOW(),"%D")	23rd
SELECT DATE_FORMAT(NOW(),"%d")	23
SELECT DATE_FORMAT(NOW(),"%e")	23
SELECT DATE_FORMAT(NOW(),"%f")	0
SELECT DATE_FORMAT(NOW(),"%H")	19
SELECT DATE_FORMAT(NOW(),"%h")	7
SELECT DATE_FORMAT(NOW(),"%I")	7
SELECT DATE_FORMAT(NOW(),"%i")	45
SELECT DATE_FORMAT(NOW(),"%j")	54
SELECT DATE_FORMAT(NOW(),"%k")	19
SELECT DATE_FORMAT(NOW(),"%l")	7
SELECT DATE_FORMAT(NOW(),"%M")	February
SELECT DATE_FORMAT(NOW(),"%m")	2
SELECT DATE_FORMAT(NOW(),"%p")	PM
SELECT DATE_FORMAT(NOW(),"%r")	7:45:14 PM
SELECT DATE_FORMAT(NOW(),"%S")	14
SELECT DATE_FORMAT(NOW(),"%s")	14
SELECT DATE_FORMAT(NOW(),"%T")	19:45:14
SELECT DATE_FORMAT(NOW(),"%U")	8
SELECT DATE_FORMAT(NOW(),"%u")	8
SELECT DATE_FORMAT(NOW(),"%V")	8
SELECT DATE_FORMAT(NOW(),"%v")	8
SELECT DATE_FORMAT(NOW(),"%W")	Sunday
SELECT DATE_FORMAT(NOW(),"%w")	0
SELECT DATE_FORMAT(NOW(),"%X")	2020
SELECT DATE_FORMAT(NOW(),"%x")	2020
SELECT DATE_FORMAT(NOW(),"%Y")	2020
SELECT DATE_FORMAT(NOW(),"%y")	20

Redshift query to generate UNLOAD and COPY statements

Below is a statement that can be used to generate a list of commands to copy all tables from Redshift to S3.  Please note that you'll need to substitute YOUR_BUCKET_NAME, YOUR_ACCOUNT_ID and YOUR_ROLE placeholders with the values appropriate for your environment.

SELECT
' UNLOAD (''SELECT * FROM ' ||
"table" ||
''')' ||
' TO ''s3://YOUR_BUCKET_NAME/' ||
"table" ||
'/''' ||
' iam_role 'arn:aws:iam::YOUR_ACCOUNT_ID:role/YOUR_ROLE''' ||
' delimter ''|'' addquotes escape allowoverwrite;'
FROM SVV_TABLE_INFO;

In a situation where you want use to copy data into a different redshift cluster, the following will create the copy commands that can be used to copy data from S3 to Redshift.  Please note that you'll need to substitute YOUR_BUCKET_NAME, YOUR_ACCOUNT_ID and YOUR_ROLE placeholders with the values appropriate for your environment.

SELECT
' COPY ' ||
"table" ||
' FROM ''s3://YOUR_BUCKET_NAME/' ||
"table" ||
'/''' ||
' iam_role ''arn:aws:iam::YOUR_ACCOUNT_ID:role/YOUR_ROLE''' ||
' delimiter ''|'' removequotes escape '';'
FROM SVV_TABLE_INFO;

SQL Server Date Formatting

I always feel like I'm digging around to find a list of date formats for SQL Server.  I then end up doing some trial and error to get the desired format.

Below is helpful list to refer to for common date formatting.

Format Code	Output
default	2011-10-20 06:59:22.590
convert(varchar,getDate(),100)	Oct 20 2011 6:59AM
convert(varchar,getDate(),101)	10/20/2011
convert(varchar,getDate(),102)	2011.10.20
convert(varchar,getDate(),103)	20/10/2011
convert(varchar,getDate(),104)	20.10.2011
convert(varchar,getDate(),105)	20-10-2011
convert(varchar,getDate(),106)	20 Oct 2011
convert(varchar,getDate(),107)	Oct 20, 2011
convert(varchar,getDate(),108)	06:59:00
convert(varchar,getDate(),109)	Oct 20 2011 6:59:00:553AM
convert(varchar,getDate(),110)	10-20-2011
convert(varchar,getDate(),111)	2011/10/20
convert(varchar,getDate(),112)	20111020
convert(varchar,getDate(),113)	20 Oct 2011 06:59:00:553
convert(varchar,getDate(),114)	06:59:00:553
convert(varchar,getDate(),120)	2011-10-20 06:59:00
convert(varchar,getDate(),121)	2011-10-20 06:59:00.553
convert(varchar,getDate(),126)	2011-10-20T06:59:00.553
convert(varchar,getDate(),127)	2011-10-20T06:59:00.553
convert(varchar,getDate(),130)	23 ?? ?????? 1432 6:59:00:553
convert(varchar,getDate(),131)	23/11/1432 6:59:00:553AM