DateTime functions in SQL Server Part 25 — Transcript

Full transcript

1. 0:00hello welcome to preim Technologies I'm
2. 0:02wenet this is part 25 of SQL server in
3. 0:05this session we'll learn about datetime
4. 0:08data types date time functions and
5. 0:11understand the terms UTC time and time
6. 0:13zone
7. 0:14offset in SQL Server there are several
8. 0:17data types as far as date time is
9. 0:19concerned time date small date time Etc
10. 0:24now depending on the requirement of the
11. 0:26application we choose the appropriate
12. 0:28date time data type for example if I
13. 0:32want to store only the date of birth of
14. 0:35a person then it's enough if I choose
15. 0:37the date data type on the other hand
16. 0:40instead of choosing date data type if
17. 0:42you choose date time you know let's say
18. 0:45I want to store only the date of birth I
19. 0:48don't want to capture the time but
20. 0:50instead of choosing date data type I've
21. 0:53chosen data type date time data type
22. 0:56what is the disadvantage of doing so if
23. 0:58you look at datetime data type it has
24. 1:00got eight bytes the size but whereas
25. 1:03date data type is only three bytes so
26. 1:06when it's not required to capture the
27. 1:08time then probably it's you know it
28. 1:11makes sense to choose the date data type
29. 1:14alone rather than choosing date time
30. 1:16data type so on the other hand let's say
31. 1:19I want to store the time as well maybe
32. 1:21that's when we choose the date time data
33. 1:24type okay maybe I want to store only the
34. 1:26time in those cases maybe I'll choose
35. 1:28the time data type
36. 1:30okay or if I want more Precision for the
37. 1:33Nan seconds then I choose dat time to
38. 1:37data type so here year month date hours
39. 1:41milliseconds I mean minutes seconds and
40. 1:45Nano second so there is more Precision
41. 1:48for nanocs here in date time too when we
42. 1:51compare that with date time okay now we
43. 1:54have this daytime offset let us
44. 1:57understand what we mean by daytime
45. 1:59offset now if if you look at the date
46. 2:00time offset it's pretty much similar to
47. 2:03date time two okay we have years month
48. 2:08days and then hours minutes seconds Nan
49. 2:11seconds and then you have CAU some plus
50. 2:13you know optionally plus or minus symbol
51. 2:16and then you've got hours and minutes
52. 2:18okay so this piece here the plus or
53. 2:22minus hours and minutes this is called
54. 2:24the time zone
55. 2:27offset and this time you know when you
56. 2:30use date time offset data type this time
57. 2:33is usually you know in UTC time okay
58. 2:39this Falls in UTC time zone now let us
59. 2:42understand what exactly do we mean by
60. 2:44UTC time and you know the time zone
61. 2:49offset
62. 2:51okay so UTC basically stands for
63. 2:54coordinated universal time now here is
64. 2:57another confusion UTC but if if you look
65. 3:00at coordinated universal time it is
66. 3:03C so how is this an expanded form of
67. 3:06this abbreviation now there are some
68. 3:08political reasons behind why they have
69. 3:10actually abbreviated it like this but
70. 3:12actually this stands for coordinated
71. 3:14universal time now we'll understand the
72. 3:16concepts behind what do we mean by
73. 3:19coordinated universal time and uh the
74. 3:22time zone offset rather than worrying
75. 3:24about the political reasons behind
76. 3:26abbreviating it like that so UTC stands
77. 3:29for coordin ated universal time so
78. 3:32basically this UTC is is the you know is
79. 3:38the central Time based on which the
80. 3:40world regulates clocks and time okay now
81. 3:45UTC and there is another time zone
82. 3:47called GMT green wi meantime okay
83. 3:50they're actually synonymous with each
84. 3:53other for all common purposes but
85. 3:56however there are very slight
86. 3:57differences you know uh but most of the
87. 4:00time gmta and UTC are are synonymous for
88. 4:04all common
89. 4:06purposes okay so what do we mean by the
90. 4:09cutc okay to understand this practically
91. 4:12you know all of us have worked with the
92. 4:14clock and Windows operating system so if
93. 4:16I open the clock in Windows operating
94. 4:18system let's say I want to change the
95. 4:21date and time settings I click that and
96. 4:23when we go to change time zone so if you
97. 4:26look at this time zone here and if I
98. 4:29draw this list down you look at this
99. 4:32these are all the time zones if not all
100. 4:35most of the time zones in the world okay
101. 4:38now if you look at this there is
102. 4:41something called UTC here which is
103. 4:45nothing but coordinated universal time
104. 4:48okay and then if you look some of these
105. 4:50here so UTC plus 2 hours UTC plus 3
106. 4:55hours okay so this UTC is the Cod
107. 4:59coordinated universal time the central I
108. 5:02can say the world Standard Time based on
109. 5:05which other clocks and times in
110. 5:08different time zones are
111. 5:10calculated okay so if in UTC the time is
112. 5:1412:00 at the minute then if we want to
113. 5:18calculate what is the time in Germany
114. 5:21which is Berlin so if it is 12:00 in UTC
115. 5:24coordinated universal time in Germany
116. 5:27it's 12 hours I mean 12 in the noon + 1
117. 5:31which means 13 hours 1:00 in the
118. 5:34afternoon in Germany okay on the other
119. 5:36hand if we want to find out what's the
120. 5:38time in New Delhi so New Delhi is around
121. 5:425 and 1 half hours from UTC which is
122. 5:45India so the time in I mean in UTC time
123. 5:49zone if it's 12:00 uh then it's 5:30 in
124. 5:54the evening in
125. 5:55India okay so all the other time
126. 6:00zones are based on UTC so so that plus
127. 6:05or minus hours so if you have to look at
128. 6:08the countries on the negative side I
129. 6:10mean on the minus side UTC minus
130. 6:13something look at this um
131. 6:16Central Central America is UTC minus 6
132. 6:21hours so this minus plus or minus thing
133. 6:24is nothing but the offset from the um
134. 6:28UTC time
135. 6:31zone okay now I'm right now in London so
136. 6:34if you look at the time right now in
137. 6:36London it is
138. 6:372218 now let me try to switch my time
139. 6:40zone to UTC and see what happens so when
140. 6:44I select UTC coordinated universal time
141. 6:48look at this uh current date and time on
142. 6:51my machine is 228 which is 10:00 in the
143. 6:55night okay but UTC time is is
144. 7:002119 which means London's time is
145. 7:03actually 1 hour ahead of the UTC time
146. 7:07okay but if you look at the London time
147. 7:09zone here we don't have that plus 1 hour
148. 7:13extra here okay it just says okay UTC
149. 7:17coordinated universal time and then UTC
150. 7:20uh Dublin edenburg Lisbon London okay
151. 7:23but then if you look at the time that I
152. 7:25have selected there is 1 hour difference
153. 7:27okay there was 1 hour difference
154. 7:32so if I select London let's cancel that
155. 7:35let's go back change date and time
156. 7:38change time zone look at this it's
157. 7:412219 now if I select UTC time so there's
158. 7:461 hour difference between UTC and London
159. 7:50but then it doesn't show up in that
160. 7:51drop- down list so why is
161. 7:55that that's because there is another
162. 7:57concept called daylight s savings okay
163. 8:01so since this is summer here here um we
164. 8:04actually have moved our clocks you know
165. 8:081
166. 8:09hour you know 1 hour forward because of
167. 8:13which we are getting that difference
168. 8:15between uh C I mean the UTC time and the
169. 8:19time in London otherwise you know in
170. 8:22winter again we'll move it backwards and
171. 8:24UTC time matches with London's time so
172. 8:27that's why daylight savings are not
173. 8:28taken into conation here that's the
174. 8:30reason why you see uh that difference
175. 8:33when I change the data date
176. 8:35here okay so I I hope it's clear what is
177. 8:39UTC time and time
178. 8:42zone
179. 8:44okay all right so in order to understand
180. 8:48these different data types I've created
181. 8:49a very simple table here um called tbll
182. 8:53date time and I have used all the
183. 8:56datetime data types that are available
184. 8:58you know time date small dat time dat
185. 9:00time Etc in this table just to show you
186. 9:03practically how the data get stored in
187. 9:05the table so if you look at this we have
188. 9:09this column C time which is time data
189. 9:12type c dat is just date data type small
190. 9:16date time is small date time I have
191. 9:18include ccore just to you know give it a
192. 9:22you know ccore indicates column in that
193. 9:25table it's about that so when I select
194. 9:28the all the rows from this table we
195. 9:29don't have any data in those columns
196. 9:32okay let's try and insert some data into
197. 9:35these columns okay now look at this in
198. 9:37order to get a system date and time the
199. 9:41current date and time in SQL Server I
200. 9:43can use a function called get date we
201. 9:46have spoken about string many string
202. 9:48functions for example substring lower
203. 9:51upper just like all these functions to
204. 9:53work with datetime data types we have
205. 9:56datetime functions and one of the very
206. 9:58common functions that we use is get date
207. 10:00which will basically give us the current
208. 10:02system date and time so when I execute
209. 10:04that I get the current system date and
210. 10:06time now I'm using this function instead
211. 10:08of typing in the values for time date
212. 10:12small date time you know for all these
213. 10:14columns I am actually using this method
214. 10:18okay so it's going to return if you look
215. 10:20at this select get date it's going to
216. 10:23return the current system date and time
217. 10:26okay year month days hour minutes
218. 10:30seconds and milliseconds I mean nanocs
219. 10:32rather okay so now when I try to execute
220. 10:36this
221. 10:38query so one row affected which means
222. 10:40one row got inserted into this table
223. 10:43called TBL day time so if I select this
224. 10:46look at this time is only storing the
225. 10:50time hours minutes seconds and the Nan
226. 10:53seconds so which is nothing but our time
227. 10:56data type hours minutes seconds and Nan
228. 10:58seconds and along the same lines if you
229. 11:00look at daytime
230. 11:01offet so it's storing year month days
231. 11:06hours minutes seconds Nan seconds and
232. 11:10the time zone offset now since get
233. 11:13date if you see the time zone offset
234. 11:16difference it's only 0 0 here that's
235. 11:18because when I say get date it doesn't
236. 11:21give any value for time zone offset so
237. 11:24that's why you see the time zone offset
238. 11:26difference is plus 0 0 on the other hand
239. 11:29if you want to update that you can
240. 11:31update with any time zone offset but if
241. 11:34you want to store the time zone offset
242. 11:36in daytime to data type or datetime data
243. 11:39type it's not practically possible it
244. 11:41doesn't allow you to do
245. 11:43that okay so let's quickly update it
246. 11:47with some
247. 11:48offset so I want to say the offset is
248. 11:52maybe plus one hour for this time I've
249. 11:56just written the update query beforehand
250. 11:58you know in order to save time in typing
251. 12:01so when I press F5 you should see we got
252. 12:04an offset there and know it's 10 hours
253. 12:08okay all right
254. 12:13cool okay so these are some of the
255. 12:15functions that we can use in SQL Server
256. 12:18to get the current system date and time
257. 12:22in SQL Server you know we have just seen
258. 12:24how to use geted function to return the
259. 12:27current system date and time okay on my
260. 12:30computer I have SQL Server installed so
261. 12:33on the computer where you have SQL
262. 12:34Server installed if you want to get the
263. 12:36time and date from that machine then you
264. 12:39can use select get date okay let's say
265. 12:41if I am connected to SQL server using a
266. 12:43SQL Server management Studio from a
267. 12:45different computer and when I connect to
268. 12:48that SQL Server from SQL Server
269. 12:49management Studio installed on that
270. 12:52client computer and when I execute
271. 12:53select get date I get the date and time
272. 12:57of the SE of where you have the SQL
273. 13:00Server installed rather than on your
274. 13:02computer from which you have connected
275. 13:05to SQL
276. 13:06Server okay so apart from get date you
277. 13:09can also use various other functions
278. 13:11like current time stamp system date time
279. 13:13system date time offset get UTC date and
280. 13:16sis UTC date time so if you look at
281. 13:20these functions they written the current
282. 13:22date and time in different formats okay
283. 13:25so for example we have just spoken about
284. 13:28what is UTC time and the time zone
285. 13:31offset so if you want you know maybe um
286. 13:36the time zone offset as well you can use
287. 13:39system s date time offset that will give
288. 13:42you the time in UTC I mean date time in
289. 13:45UTC and then the offset the
290. 13:49difference okay so if you look
291. 13:52at
292. 13:54says says date time offset so I've got
293. 13:57the time and UT TC and 1 hour is the
294. 14:01difference now right now in London the
295. 14:03time is
296. 14:062226 okay which is 1 hour
297. 14:11ahead of the UTC
298. 14:17time okay so along the same lines you
299. 14:20have these other functions so depending
300. 14:21on on your requirement how you want to
301. 14:24retrieve the date and time you choose
302. 14:27that function so and out of these
303. 14:30functions get dat is very commonly used
304. 14:32if you look at current timestamp and get
305. 14:34dat they written exactly the same date
306. 14:36and time format the only difference
307. 14:38between them is current time stamp is an
308. 14:41an SQL equivalent to get date okay
309. 14:44whereas CIS date time gives us more
310. 14:47fractional seconds
311. 14:49Precision okay CIS date time offset
312. 14:52which we have just say you know you have
313. 14:54more fractional seconds Precision plus
314. 14:57the time zone offset
315. 14:59and if you want the UTC date and time
316. 15:01you use get UTC date time and along the
317. 15:05same lines sis UTC date
318. 15:08time okay which has more fractional
319. 15:11seconds
320. 15:13Precision along with UTC date
321. 15:16time on this slide you can find
322. 15:18resources for asp.net and C interview
323. 15:21questions that's it for today thank you
324. 15:23for listening have a great day