Date Format
|
Code
|
SQL Statement
|
Sample output
|
mon dd yyyy
hh:miAM (or PM)
|
0
|
SELECT CONVERT(VARCHAR(20),GETDATE(),0)
|
Nov 25 2014
2:13PM
|
100
|
SELECT CONVERT(VARCHAR(20),GETDATE(),100)
|
||
mm/dd/yy
|
1
|
SELECT CONVERT(VARCHAR(20),GETDATE(),1)
|
11/25/14
|
mm/dd/yyyy
|
101
|
SELECT CONVERT(VARCHAR(20),GETDATE(),101)
|
11/25/2014
|
yy.mm.dd
|
2
|
SELECT CONVERT(VARCHAR(20),GETDATE(),2)
|
14.11.25
|
yyyy.mm.dd
|
102
|
SELECT CONVERT(VARCHAR(20),GETDATE(),102)
|
2014.11.25
|
dd/mm/yy
|
3
|
SELECT CONVERT(VARCHAR(20),GETDATE(),3)
|
25/11/14
|
dd/mm/yyyy
|
103
|
SELECT CONVERT(VARCHAR(20),GETDATE(),103)
|
25/11/2014
|
dd.mm.yy
|
4
|
SELECT CONVERT(VARCHAR(20),GETDATE(),4)
|
25.11.14
|
dd.mm.yyyy
|
104
|
SELECT CONVERT(VARCHAR(20),GETDATE(),104)
|
25.11.2014
|
dd-mm-yy
|
5
|
SELECT CONVERT(VARCHAR(20),GETDATE(),5)
|
25-11-14
|
dd-mm-yyyy
|
105
|
SELECT CONVERT(VARCHAR(20),GETDATE(),105)
|
25-11-2014
|
dd mon yy
|
6
|
SELECT CONVERT(VARCHAR(20),GETDATE(),6)
|
25 Nov 14
|
dd mon yyyy
|
106
|
SELECT CONVERT(VARCHAR(20),GETDATE(),106)
|
25 Nov 2014
|
Mon dd, yy
|
7
|
SELECT CONVERT(VARCHAR(20),GETDATE(),7)
|
Nov 25, 14
|
Mon dd, yyyy
|
107
|
SELECT CONVERT(VARCHAR(20),GETDATE(),107)
|
Nov 25, 2014
|
hh:mi:ss
|
8
|
SELECT CONVERT(VARCHAR(20),GETDATE(),8)
|
15:17:41
|
hh:mi:ss
|
108
|
SELECT CONVERT(VARCHAR(20),GETDATE(),108)
|
15:17:41
|
mon dd yyyy
hh:mi:ss:mmmAM
(or PM)
|
9
|
SELECT CONVERT(VARCHAR(30),GETDATE(),9)
|
Nov 25 2014
3:26:56:273PM
|
109
|
SELECT CONVERT(VARCHAR(30),GETDATE(),109)
|
||
mm-dd-yy
|
10
|
SELECT CONVERT(VARCHAR(20),GETDATE(),10)
|
11-25-14
|
mm-dd-yyyy
|
110
|
SELECT CONVERT(VARCHAR(20),GETDATE(),110)
|
11-25-2014
|
yy/mm/dd
|
11
|
SELECT CONVERT(VARCHAR(20),GETDATE(),11)
|
14/11/25
|
yyyy/mm/dd
|
111
|
SELECT CONVERT(VARCHAR(20),GETDATE(),111)
|
2014/11/25
|
yymmdd
|
12
|
SELECT CONVERT(VARCHAR(20),GETDATE(),12)
|
141125
|
yyyymmdd
|
112
|
SELECT CONVERT(VARCHAR(20),GETDATE(),112)
|
20141125
|
dd mon yyyy
hh:mi:ss:mmm(24h)
|
13
|
SELECT CONVERT(VARCHAR(30),GETDATE(),13)
|
25 Nov 2014
15:28:11:493
|
113
|
SELECT CONVERT(VARCHAR(30),GETDATE(),113)
|
||
hh:mi:ss:mmm(24h)
|
14
|
SELECT CONVERT(VARCHAR(20),GETDATE(),14)
|
15:29:54:083
|
114
|
SELECT CONVERT(VARCHAR(20),GETDATE(),114)
|
||
yyyy-mm-dd
hh:mi:ss(24h)
|
20
|
SELECT CONVERT(VARCHAR(20),GETDATE(),20)
|
2014-11-25
15:31:40
|
120
|
SELECT CONVERT(VARCHAR(20),GETDATE(),120)
|
||
yyyy-mm-dd
hh:mi:ss.mmm(24h)
|
21
|
SELECT CONVERT(VARCHAR(30),GETDATE(),21)
|
2014-11-25
15:33:15.337
|
121
|
SELECT CONVERT(VARCHAR(30),GETDATE(),121)
|
||
yyyy-mm-
ddThh:mi:ss.mmm
(no spaces)
|
126
|
SELECT CONVERT(VARCHAR(30),GETDATE(),126)
|
2014-11-
25T15:34:36.280
|
| yyyy-mm- ddThh:mi:ss.mmmZ (no spaces) |
127
|
SELECT CONVERT(VARCHAR(30),GETDATE(),127)
|
2014-11-
25T15:35:41.170
|
dd mon yyyy
hh:mi:ss:mmmAM
|
130
|
SELECT CONVERT(VARCHAR(30),GETDATE(),130)
|
25 Nov 2014
3:36:29:180PM
|
dd/mm/yy
hh:mi:ss:mmmAM
|
SELECT CONVERT(VARCHAR(30),GETDATE(),131)
|
3/02/1436
3:38:40:430PM
|
Tuesday, 25 November 2014
SQL Server Date Formates
Saturday, 22 November 2014
Multi row subquery
Single row subquery
In Single row subquery, inner query returns 'Zero or One' row. Based on the inner query result outer query will return output. The operators that can be used with single-row subqueires are =, >, >=, <, <=, and <>.
Example:
SELECT * FROM EMPLOYEE
WHERE Dept_ID =
(
SELECT
DEPTID
FROM DEPT
WHERE DeptID = 10
)Thursday, 20 November 2014
Rename Column
Using SP_RENAME we can rename columns.
Rename Column:
Syntax:
SP_RENAME '<Database name>.<Schema name>.<Table Name>.<Column Name>','<New Name>'
Example:
Table Creation:
CREATE TABLE
SAMPLE14
(
ID INT ,
NAME VARCHAR(10),
TYPE_DESC VARCHAR(50)
) Renaming Table:
SP_RENAME 'KIRAN.DBO.SAMPLE14.ID','EMP_ID'
Rename table
Using SP_RENAME we can rename SQL Server data base objects (Tables, Views, Functions and Procedures). Using SP_RENAME we can rename columns also.
Rename table:
Syntax:
SP_RENAME '<Database name>.<Schema name>.<Table Name>','<New Name>'
Example:
Table Creation:
CREATE TABLE
SAMPLE13
(
ID INT ,
NAME VARCHAR(10),
TYPE_DESC VARCHAR(50)
) Renaming Table:
SP_RENAME 'KIRAN.DBO.SAMPLE13','TEST_TBL'
Wednesday, 19 November 2014
Indexes
Subscribe to:
Posts (Atom)