T-SQL – How to output ordinal number – 1ST 2ND 3RD 4TH
The following T-SQL statement shows how output a number as an ordinal number (st, nd, rd, th). For example, 3 would output 3rd. How do I use this? Replace '3'…
VARCHAR
T-SQL – How to output ordinal day from date – 1ST 2ND 3RD 4TH
The following T-SQL statement shows how output the day from a date as an ordinal number (st, nd, rd, th). For example, 16 March 2019 would output 16th. How do…
SQL – How to convert datetime to formatted date string dd-mm-yyyy
When you have a date or datetime column in an SQL database, it will output a value that looks like 2017-01-01 00:00:00.000 This is the standard format used for datetime columns…
SQL – How to select current year from lookup table
The following SQL shows how to select a row from a table using the current year as the WHERE condition. This is useful if you have a table of years…
SQL – How to get current financial year using SQL
The following SQL shows how to get the current financial year using SQL. This assumes that the financial year for your country is 1 July - 30 June. This SQL…
SQL – How to select current financial year from lookup table
The following SQL shows how to select a row from a table using the current financial year as the WHERE condition. This is useful if you have a table of…
SQL – How to concatenate/combine column values into comma separated text string
The following SQL statement shows how to combine a column into a single text string - separated by a comma. For example: Name Fred Jim Jane Betty Would be combined…
SQL – How to insert new line/line break in string
If you're building a string using an SQL statement, you can split results with "new lines" using CHAR() to insert the ASCII line break character into your string. For example,…
SQL – How to decode HTML entities when using FOR XML PATH
When building complicated SQL statements often I will make use of a SELECT - FOR XML PATH statement to concatenate many results into a single string. For example SELECT STUFF(…