Transact-SQL

Tips and tricks to make my SQL coding a little easier without the need for reference books.

Date Conversion

Name Style Format
Default0,100Mon dd yyyy hh:mi [AM:PM]
USA1mm/dd/yy
 101mm/dd/yyyy
British/French3dd/mm/yy
 103dd/mm/yyyy
German4dd.mm.yy
 104dd.mm.yyyy
Italian5dd-mm-yy
 105dd-mm-yyyy
 6dd Mon yy
 106dd Mon yyyy
 7Mon dd yy
 107Mon dd yyyy
 8,108hh:mi:ss
Default9,109Mon dd yyyy hh:mi:ss:mmm [AM:PM]
USA10mm-dd-yy
 110mm-dd-yyyy
Japan11yy/mm/dd
 111yyyy/mm/dd
ISO12yymmdd
 112yyyymmdd
Europe13,113dd Mon yyyy
 14,114hh:mi:ss:mm

Example:

convert(char(10),ord_date,3)

where ord_date is defined as "datetime".

Finding Minimums and Maximums

If there are chunks of data and we want to process them chunk by chunk, like so:

BookID Title Shop Price
0001 The Catcher In The Rye Amazon.com 6.99
0001 The Catcher In The Rye Books Etc. 7.25
0001 The Catcher In The Rye Borders 7.15
0005 To Kill A Mockingbird Waterstones 8.00
0005 To Kill A Mockingbird ABC Books 8.25
0005 To Kill A Mockingbird Borders 7.99

If we want an additional column that will list the cheapest price for each book listed, we have to: select BookID, Title, Shop, min(Price) from BookTable group by BookID