What
are the differences between SMALLDATETIME and DATETIME [SMALLDATETIME VS
DATETIME]?
Which
data type should use for defining a column in a table [SMALLDATETIME VS
DATETIME]?
SMALLDATETIME
|
DATETIME
|
|
Bytes
|
It consumes 4 bytes.
|
It consumes 8 bytes.
|
Minimum Value
|
1900-01-01 00:00:00
YYYY-mm-DD HH:MM:SS
Data accuracy max in
seconds.
Round off logic for
seconds:
29.998 sec = 29:00
seconds
29.999 sec or more =
30:00 seconds
|
1753-01-01
00:00:00.000
YYYY-mm-DD HH:MM:SS.ms
Data accuracy is
maximum 3 digits of milliseconds.
Rounded to increments
of .000, .003, or .007 second
23:59:58.990 or
23:59:58.991 = 23:59:58.990
23:59:58.992 or
23:59:58.994 = 23:59:58.993
|
Maximum Value
|
2079-12-31 23:59:00
|
9999-12-31 23:59:59.997
|
Data
Storage
Type
|
Used to store the date
time data without fractional seconds
precision.
|
Used to store the date
time data with maximum 3 digits fractional seconds precision.
|
Storage
|
Storage size is fixed 4 bytes.
|
Storage size is fixed 8 bytes.
|
Use When?
|
When fractional
seconds precision is not needed and
date value does not exceed 2079-12-31 23:59:00.
|
When 3 digits fractional
seconds precision and date value exceeds 2079-12-31 23:59:00.
SELECT GETDATE()
OUTPUT:
2016-11-24 17:25:22.040
|
Optional Parameter
value
|
No optional parameter for this data type.
DECLARE @Now SMALLDATETIME = GETDATE()
PRINT @Now
OUTPUT:
Nov 24 2016 5:30PM
|
No optional parameter for this data type.
DECLARE @Now DATETIME = GETDATE()
PRINT @Now
OUTPUT:
Nov 24 2016 5:30PM
|
+ OR - Days
|
It supports + or – for adding to or subtracting from
and also use DATEADD function to add or subtract.
DECLARE @Now SMALLDATETIME
SET @Now = DATEADD(D, -1, GETDATE())
PRINT @Now
OUTPUT:
Nov 23 2016 6:29PM
DECLARE @Now SMALLDATETIME
SET @Now = DATEADD(D, -96000, GETDATE())
PRINT @Now
OUTPUT:
ERROR:
Msg 242, Level 16, State 3, Line 2
The conversion of
a datetime data type to a smalldatetime data type resulted in an out-of-range
value
|
It supports + or – for adding to or subtracting from
and also use DATEADD function to add or subtract.
DECLARE @Now DATETIME = DATEADD(D, -96000, GETDATE())
PRINT @Now
OUTPUT:
Jan 22 1754 5:43PM
DECLARE @Now DATETIME = GETDATE() - 96000
PRINT @Now
OUTPUT:
Jan 22 1754 5:43PM
|
Date and Time Styles
SQL Server supports the following
various date format.
|
||
Symbol
|
Expression
|
Output Date Format
|
1
|
CONVERT(VARCHAR(20), GETDATE(), 1)
|
11/30/16
|
2
|
CONVERT(VARCHAR(20), GETDATE(), 2)
|
16.11.30
|
3
|
CONVERT(VARCHAR(20), GETDATE(), 3)
|
30/11/16
|
4
|
CONVERT(VARCHAR(20), GETDATE(), 4)
|
30.11.16
|
5
|
CONVERT(VARCHAR(20), GETDATE(), 5)
|
30-11-16
|
6
|
CONVERT(VARCHAR(20), GETDATE(), 6)
|
30 Nov 16
|
7
|
CONVERT(VARCHAR(20), GETDATE(), 7)
|
Nov 30, 16
|
8
|
CONVERT(VARCHAR(20), GETDATE(), 8)
|
13:12:44
|
9
|
CONVERT(VARCHAR(20), GETDATE(), 9)
|
Nov 30 2016
1:12:44:673PM
|
10
|
CONVERT(VARCHAR(20), GETDATE(), 10)
|
11-30-16
|
11
|
CONVERT(VARCHAR(20), GETDATE(), 11)
|
16/11/30
|
12
|
CONVERT(VARCHAR(20), GETDATE(), 12)
|
161130
|
13
|
CONVERT(VARCHAR(30), GETDATE(), 13)
|
30 Nov 2016 13:12:44:673
|
14
|
CONVERT(VARCHAR(30), GETDATE(), 14)
|
13:12:44:673
|
20
|
CONVERT(VARCHAR(30), GETDATE(), 20)
|
2016-11-30 13:12:44
|
21
|
CONVERT(VARCHAR(30), GETDATE(), 21)
|
2016-11-30 13:12:44.673
|
101
|
CONVERT(VARCHAR(20), GETDATE(), 101)
|
11/30/2016
|
102
|
CONVERT(VARCHAR(20), GETDATE(), 102)
|
2016.11.30
|
103
|
CONVERT(VARCHAR(20), GETDATE(), 103)
|
30/11/2016
|
104
|
CONVERT(VARCHAR(20), GETDATE(), 104)
|
30.11.2016
|
105
|
CONVERT(VARCHAR(20), GETDATE(), 105)
|
30-11-2016
|
106
|
CONVERT(VARCHAR(20), GETDATE(), 106)
|
30 Nov 2016
|
107
|
CONVERT(VARCHAR(20), GETDATE(), 107)
|
Nov 30, 2016
|
108
|
CONVERT(VARCHAR(20), GETDATE(), 108)
|
13:12:44
|
109
|
CONVERT(VARCHAR(20), GETDATE(), 109)
|
Nov 30 2016
1:12:44:673PM
|
110
|
CONVERT(VARCHAR(20), GETDATE(), 110)
|
11-30-2016
|
111
|
CONVERT(VARCHAR(20), GETDATE(), 111)
|
2016/11/30
|
112
|
CONVERT(VARCHAR(20), GETDATE(), 112)
|
20161130
|
113
|
CONVERT(VARCHAR(30), GETDATE(), 113)
|
30 Nov 2016 13:12:44:673
|
114
|
CONVERT(VARCHAR(30), GETDATE(), 114)
|
13:12:44:673
|
120
|
CONVERT(VARCHAR(30), GETDATE(), 120)
|
2016-11-30 13:12:44
|
121
|
CONVERT(VARCHAR(30), GETDATE(), 121)
|
2016-11-30 13:12:44.673
|
126
|
CONVERT(VARCHAR(30), GETDATE(), 126)
|
2016-11-30T13:12:44.673
|
127
|
CONVERT(VARCHAR(30), GETDATE(), 127)
|
2016-11-30T13:12:44.673
|
130
|
CONVERT(NVARCHAR(30), GETDATE(), 130)
|
1 ربيع الاول 1438 1:12:44:67
|
131
|
CONVERT(VARCHAR(30), GETDATE(), 131)
|
1/03/1438
1:12:44:673PM
|
No comments:
Post a Comment