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
Advertisements

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

Summarizes All of The System Databases.

SQL Server maintains a set of system-level databases, system databases, which are essential for the operation of a server instance. Several of the system databases must be backed up after every significant update. The system databases that you must always back up include msdbmaster, and model. If any database uses replication on the server instance, there is a distribution system database that you must also back up. Backups of these system databases let you restore and recover the SQL Server system in the event of system failure, such as the loss of a hard disk.

The following table summarizes all of the system databases.

System database Description Are backups required? Recovery model Comments
master The database that records all of the system level information for a SQL Server system. Yes Simple Back up master as often as necessary to protect the data sufficiently for your business needs. We recommend a regular backup schedule, which you can supplement with an additional backup after a substantial update.
model The template for all databases that are created on the instance of SQL Server. Yes User configurable1 Back up model only when necessary for your business needs; for example, immediately after customizing its database options.Best practice:  We recommend that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary.

 

msdb The database used by SQL Server Agent for scheduling alerts and jobs, and for recording operators. msdb also contains history tables such as the backup and restore history tables. Yes Simple (default) Back up msdb whenever it is updated.
Resource(RDB) A read-only database that contains copies of all system objects that ship with Microsoft SQL Server 2005 or later versions. No The Resource database resides in the mssqlsystemresource.mdf file, which contains only code. Therefore, SQL Server cannot back up the Resource database.

NoteNote
You can perform a file-based or a disk-based backup on the mssqlsystemresource.mdf file by treating the file as if it were a binary (.exe) file, instead of a database file. But you cannot use SQL Server restore on the backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resourcedatabase with an out-of-date or potentially insecure version.
tempdb A workspace for holding temporary or intermediate result sets. This database is re-created every time an instance of SQL Server is started. When the server instance is shut down, any data in tempdb is deleted permanently. No Simple You cannot back up the tempdb system database.
Configuring Distribution A database that exists only if the server is configured as a replication Distributor. This database stores metadata and history data for all types of replication, and transactions for transactional replication. Yes Simple For information about when to back up thedistribution database,