The DATEPART function on SQL Server

The DATEPART function.

This function will take a date/time expression and return a single part of the date, such as hour, minute, month, day, etc. The syntax for using DATEPART is:

Quote

DATEPART(datepart, date)

Where the datepart is one of the following:

• Year: yy, yyyy
• Quarter: qq, q
• Month: mm, m
• dayofyear: dy, y
• Day: dd, d
• Week: wk, ww
• Weekday: dw
• Hour: hh
• Minute: mi, n
• Second: ss, s
• Millisecond: ms.

let say the date right now is 23-03-2012

if we want to display the day, use :

DECLARE @date DATETIME
SET @date = '2012-03-23 10:31 PM'
SELECT DATEPART(dd,@date)
--- the query will return = 23
---------------------------------------

if we want to display the Month, use :

DECLARE @date DATETIME
SET @date = '2012-03-23 10:31 PM'
SELECT DATEPART(mm,@date)
--- the query will return = 03
---------------------------------------

if we want to display the Year, use :

DECLARE @date DATETIME
SET @date = '2012-03-23 10:31 PM'
SELECT DATPART(yyyy,@date)
--- the query will return = 2012
ETC

SQL Server Date Formats

One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format.  Here’s a summary of the different date formats that come standard in SQL Server as part of the CONVERT function.  Following the standard date formats are some extended date formats that are often asked by SQL Server developers.

It is worth to note that the output of these date formats are of VARCHAR data types already and not of DATETIME data type.  With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.

Standard Date Formats
SQL Statement	Sample Output
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)	Jan 1 2005 1:29PM 1
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]	11/23/98
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]	11/23/1998
SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]	72.01.01
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]	1972.01.01
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]	19/02/72
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]	19/02/1972
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]	25.12.05
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]	25.12.2005
SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]	24-01-98
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]	24-01-1998
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]	04 Jul 06
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]	04 Jul 2006
SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]	Jan 24, 98
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]	Jan 24, 1998
SELECT CONVERT(VARCHAR(8), GETDATE(), 108)	03:24:53
SELECT CONVERT(VARCHAR(26), GETDATE(), 109)	Apr 28 2006 12:32:29:253PM
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]	01-01-06
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]	01-01-2006
SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]	98/11/23
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]	1998/11/23
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]	980124
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]	19980124
SELECT CONVERT(VARCHAR(24), GETDATE(), 113)	28 Apr 2006 00:34:55:190
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]	11:34:23:013
SELECT CONVERT(VARCHAR(19), GETDATE(), 120)	1972-01-01 13:42:24
SELECT CONVERT(VARCHAR(23), GETDATE(), 121)	1972-02-19 06:35:24.489
SELECT CONVERT(VARCHAR(23), GETDATE(), 126)	1998-11-23T11:25:43:250
SELECT CONVERT(VARCHAR(26), GETDATE(), 130)	28 Apr 2006 12:39:32:429AM
SELECT CONVERT(VARCHAR(25), GETDATE(), 131)	28/04/2006 12:39:32:429AM

Display exactly 2 digits after decimal without Rounding on SQL Server

let say, we had a value 7.35642132 on sql query return, then we want to display it only exactly 2 digits after decimal without Rounding, here is the way :

to get only two digits after decimal for a value 7.35642132
Select LTRIM(RTRIM(STR(7.35642132,10,2)))

the results is 7.35

Backing up System Databases

System databases contain information about user databases as well as meta-data about SQL Server, SQL Server Agent, jobs, alerts, DTS packages and more. Therefore, it is crucial to have valid backups of the following databases:

1. Master
2. MSDB
3. Model
4. Distribution (if using replication)

Another system database, tempdb, is rebuilt each time SQL Server is started. Therefore tempdb does not need to be backed up. The Model database serves as a blue-print for creating new user databases. This database should not have any activity associated with it, other than the database administrator adding or removing objects required in all user databases. Therefore, you should typically use the SIMPLE recovery model for the Model database and only perform full backups for it.

Simple script to backup all SQL Server databases

Here is the script that will allow you to backup each database within your instance of SQL Server.  You will need to change the @path to the appropriate backup directory and each backup file will take on the name of“DBnameYYYDDMM.BAK”.

DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'C:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

In this script we are bypassing the system databases, but these could easily be included as well.  You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases.  Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.