Date Formats in SQL Server

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

 

¹ To make the month name in upper case, simply use the UPPER string function.

Extended Date Formats in SQL Server

Extended Date Formats
Date Format	SQL Statement	Sample Output
YY-MM-DD	SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]	99-01-24
	SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]
YYYY-MM-DD	SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]	1/24/1999
	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]
MM/YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]	Aug-99
	SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]
MM/YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]	Dec-05
YY/MM	SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]	99/08
YYYY/MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]	2005/12
Month DD, YYYY 1	SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]	July 04, 20061
Mon YYYY1	SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]	Apr 2006 1
Month YYYY 1	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]	February 2006 1
DD Month1	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]	11 September 1
Month DD1	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]	September 11 1
DD Month YY 1	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]	19 February 72 1
DD Month YYYY 1	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]	11 September 2002 1
MM-YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]	Dec-92
	SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]
MM-YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]	May-06
YY-MM	SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]	92/12
	SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]
YYYY-MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]	2006-05
MMDDYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]	122506
MMDDYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]	12252006
DDMMYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]	240702
DDMMYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]	24072002
Mon-YY 1	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]	Sep-02 1
Mon-YYYY1	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]	Sep-2002 1
DD-Mon-YY 1	SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]	25-Dec-05 1
DD-Mon-YYYY 1	SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]	25-Dec-20051

 

¹ To make the month name in upper case, simply use the UPPER string function.

DB_NAME (Function)

 

This returns the name of the supplied DB_ID.If no argument is passed then the name of the current database is returned.The return type is nvarchar(128).

To find the current database name:-

select DB_NAME() as Current_Database_name;

go

select DB_NAME(6) as Database_name;

go

DB_ID (Function)

 

DB_ID () function returns int valued ID of the supplied database. If no argument is provided then it return the ID of the current database.

1. To find the ID of current database:-

select DB_ID( ) as Database_ID;

go

select DB_ID(‘AdventureWorks2008R2’) ad Database_ID;

go

2. Using DB_ID to specify the value of a system function parameter

The following example uses DB_ID to return the database ID of the AdventureWorks2008R2 database in the system function sys.dm_db_index_operational_stats. The function takes a database ID as the first parameter.

DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.Person.Address');
IF @db_id IS NULL
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

Oracle Cheat Sheet

Some useful commands:-

To start a session as sysdba:
sqlplus sys@tnsname as sysdba;

To start a sysdba session under Windows (9iAS):
sqlplus "/as sysdba"

To list all tables in current schema:
SELECT table_name FROM user_tables;

or, all tables current user has access to:
SELECT table_name FROM all_tables;

To list all schemas:
SELECT username FROM all_users ORDER BY username;

To turn pause on:
SET PAUSE ON;

To list top n rows of a table in order:
SELECT * FROM (SELECT * FROM t ORDER BY c) WHERE ROWNUM <= n;

Show current database:
SELECT * FROM global_name;

Use database:
CONNECT schema/password@tnsname;

Show who I am:
SHOW USER;

Describe table:
DESC tablename;

Set display rows:
SET PAGESIZE 66;

Read field constraints:
SELECT constraint_name,search_condition FROM user_constraints WHERE table_name='tablename';

Copy table from foreign host to here:
COPY FROM user@tnsname CREATE tablename USING SELECT * FROM tablename;

Start SQLPLUS without login:
SQLPLUS /NOLOG

Change a user's password:
ALTER USER user IDENTIFIED BY password;

Unlock an account
ALTER USER user ACCOUNT UNLOCK;

Oracle has no autonumbers like SQL Server, Access, or MySQL. One way to do autonumbers is by using a combination of a sequence and a trigger, as in the following example:

CREATE SEQUENCE sequence-name;

CREATE OR REPLACE TRIGGER trigger-name
BEFORE INSERT ON table-name
FOR EACH ROW
WHEN (NEW.field-name IS NULL OR NEW.field-name = '<new>')
BEGIN
SELECT 'PR-' || sequence-name.NEXTVAL INTO :NEW.field-name FROM DUAL;
END;