SQL Server Date Convert Samples

Many times I want a particular date format but can’t remember which style it is. Below is a list of the styles and examples of how it will display. I don’t know if there are any additional Styles, if you know of any, comment and I can add them.

Usage:

CONVERT(VARCHAR, GETDATE(), 1) AS [Date]
Style Date
1 02/24/17
2 17.02.24
3 24/02/17
4 24.02.17
5 24-02-17
6 24 Feb 17
7 Feb 24, 17
8 13:10:36
9 Feb 24 2017 1:10:36:037PM
10 02-24-17
11 17/02/24
12 170224
13 24 Feb 2017 13:10:36:037
14 13:10:36:037
20 2017-02-24 13:10:36
21 2017-02-24 13:10:36.037
22 02/24/17 1:10:36 PM
23 2017-02-24
24 13:10:36
25 2017-02-24 13:10:36.037
100 Feb 24 2017 1:10PM
101 02/24/2017
102 2017.02.24
103 24/02/2017
104 24.02.2017
105 24-02-2017
106 24 Feb 2017
107 Feb 24, 2017
108 13:10:36
109 Feb 24 2017 1:10:36:037PM
110 02-24-2017
111 2017/02/24
112 20170224
113 24 Feb 2017 13:10:36:037
114 13:10:36:037
120 2017-02-24 13:10:36
121 2017-02-24 13:10:36.037
126 2017-02-24T13:10:36.037
127 2017-02-24T13:10:36.037
131 28/05/1438 1:10:36:037PM
SQL Server Date Convert Samples

SQL Server / Renaming Databases With Active Connections

hello-my-name-isI believe there are many methods and opinions that exist on the best practice to rename a SQL Server DB. Below I’ll quickly give you the method I use to rename a database. In the below example, I rename two DB’s – a common scenario if you’re swapping your production database with a different version:

-- #1 change first db name
use master
-- set db to single user
ALTER DATABASE myProdDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
-- do the rename
ALTER DATABASE myProdDB MODIFY NAME = [myOldProdDB]
-- set back to multi user
ALTER DATABASE myOldProdDB SET MULTI_USER

-- #2 change second db name
use master
-- set db to single user
ALTER DATABASE myNewProdDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
-- do the rename
ALTER DATABASE myNewProdDB MODIFY NAME = [myProdDB]
-- set back to multi user
ALTER DATABASE myProdDB SET MULTI_USER

Note: this does not change the database or log file names, just the database name.

SQL Server / Renaming Databases With Active Connections