YT YouTube2Text

YouTube2Text → Transcripts → IsDate, Day, Month, Year and DateName DateTime functions in SQL Server Part 26

IsDate, Day, Month, Year and DateName DateTime functions in SQL Server Part 26 — Transcript

by kudvenkat · 2,030 words · 283 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00well welcome to presume technologies I
0:01am venket this is part 26 of signal
0:04server in this session we'll learn about
0:07a few more date and time functions that
0:09are available specifically we'll be
0:10talking about East 8 they month year and
0:14date name functions and finally we'll
0:16look at a practical example of using all
0:19these functions together before
0:21continuing with the session I strongly
0:22recommend to watch part 25 of this video
0:25series now in the previous part we have
0:28seen the different date time data types
0:30that are available in sequel server
0:31depending on the requirement of our
0:34project we choose the date time data
0:36type that's most appropriate for example
0:38if I want to store just the date part
0:41then I use the date data dye on the
0:45other hand if I want to store the date
0:47the time including the offset
0:50information then I will choose the time
0:53offset okay so basically these are the
0:56different data types that are available
0:58in signals over look at this there is if
1:00you look at the data types here we have
1:02one called date time and the other one
1:04date time - and if you look at the
1:06difference between these two in terms of
1:08seconds for date time - we have more
1:10precision okay and we have also spoken
1:15about various other functions that are
1:17available in sequel server to retrieve
1:19the current date and time okay and we
1:23also have spoken about the UTC date and
1:27time concepts and timezone offset
1:30concepts in the previous session
1:32destroy' strongly recommend to watch
1:33part 25 of this video series before
1:36continuing with the session in this
1:38session we'll talk about ease date
1:39function so ease date function basically
1:41checks if the given value is a valid
1:45date time or date time this function
1:49returns one for success and zero for
1:52failure okay so if you look at the way
1:54we are using this function e state
1:56you're passing in something to this
1:58function in this case we are passing in
2:00pragya within single quotes and look at
2:03this can we convert this play gene into
2:05a valid date time or date time we cannot
2:08that's why this function returns zero
2:10indicating that this is not a valid date
2:13time okay but on the other hand if you
2:15look at the second example this function
2:17is taking in another function ket date
2:20and we know that get date function
2:23returns the current date and time okay
2:27so as get it returns a valid date and
2:31time we are passing it to e state
2:33function to check okay is it a valid
2:34date time obviously it's a valid date
2:37time so the function is returning one
2:38here okay and this is a hard-coded date
2:43and time value you have your month day
2:46and then hours minutes seconds and
2:49milliseconds which is a valid date and
2:51time so the e state function is
2:53returning one but in the other hand look
2:55at the fourth example that we have here
2:57it's a valid date as well the only
2:59difference between the third and the
3:01fourth date is that with a fourth date
3:03we have got more fractional seconds
3:06precision here okay but then is date
3:10function returns zero and if you look at
3:12the note here for data type for
3:15date/time values each date returns a
3:18zero
3:18okay and if you remember from the
3:21previous part anything that has got you
3:24know the long precision for the seconds
3:27it's a date/time to data type okay this
3:30is date time offset here also we have
3:33the long precision okay so for these
3:36dates each date returns false so if you
3:40look at this example here okay is date
3:44if we execute this this returns zero
3:49okay so keep that in mind each date
3:52function returns zero for day time two
3:57values okay that's about a state
4:01function and these are the examples so
4:03obviously if we execute all of them
4:05together so ease date for presume
4:09returns zero get the it returns the
4:12current date and time it's a valid date
4:14so it returns one and this is a valid
4:16date and time so that returns one the
4:19third one and finally this is a date
4:22time to data type date so that's why e
4:25State returns zero
4:26for that pretty simple to understand
4:30okay so next we will talk about day
4:33month and year functions these are very
4:35very simple to understand
4:37obviously the day function returns the
4:39day number of the month of the given
4:41date so for example if you look at the
4:44examples here I have this get date
4:47function so obviously get date returns
4:49the current date and time so if you look
4:51at the current date and time on my
4:53machine it is you know basically first
4:57of September 2012 so the day will return
5:01one here because get date returns
5:04September 1st 2012 and if you look at
5:07this here they where we have the
5:09hard-coded date the date here is 31st of
5:12January 2012 so the day function returns
5:1531 and look at the month it returns the
5:18month number of the year so obviously
5:21today is September 1st 2012
5:24so month of get date returns you know
5:27number 9 for September and here this is
5:30January 31st 2012 so month function
5:34returns 1 which is very straightforward
5:35and along the same lines iya function
5:38returns the e'en number of the given
5:40date so get date returns the current
5:42system date and time
5:43so September 1st 2012 so year part is 12
5:47I mean 2012 so this returns 2012 whereas
5:52the second one also returns 2012 because
5:55the year here is 2012 so basically
5:58depending on which part of the date you
6:00want you can choose either day month or
6:04year functions let's look at them
6:06practically real quick so obviously
6:10select get get date returns the current
6:12date and time so they should return us 9
6:15so that returns 9 and they should return
6:19that you want because 31 is the day and
6:22along the same lines
6:24today is September so when we execute
6:27this this the first method call should
6:30return 9 and here the value with the
6:32month value is hard-coded to first month
6:34so it returns 1 and if you look at the
6:38year function
6:41obviously this is 2012 September 1st
6:442012 so the first one returns 2012 and
6:47the second one returns 2012 as well
6:50because we have it hard-coded here all
6:53right so that's about day month and year
6:56functions date name function this is a
7:00very very useful function okay so date
7:03name function basically returns a string
7:06okay let's quickly prove that so if you
7:09look at this here select date name so if
7:16you look at the function itself sorry
7:21about that okay
7:28so select date night date name if you
7:30look at the function itself this
7:32function returns an N word care a string
7:35okay now basically there are two
7:39parameters that this function expects
7:41the first parameter is like which part
7:45of the day do you want I mean which part
7:47of the date do you want in string
7:49representation do you want the day or
7:52the weekday or demand okay now look at
7:56this I'm saying if you look at the
7:59second example specifically I'm calling
8:02date name function and I'm passing it
8:04weekday you know as the argument for the
8:08first parameter which is interval
8:11weekday and then I'm okay in this given
8:14date tell me for the 30th of September
8:192012
8:20what is the weekday is that a Sunday
8:22Monday Tuesday Wednesday okay so if you
8:24won't figure that out you can use date
8:26name function so when I execute this one
8:29it returns a Sunday similarly if you
8:32want to find the name of the month then
8:34you can pass month name for the interval
8:37so basically if you look at this date
8:40name function it expects two parameters
8:43okay
8:44the second parameter is the date upon
8:46which this function will operate and the
8:49first parameter you know you use that
8:51parameter to tell which part of the date
8:54do you want
8:55okay now this date part you know look at
8:58this the table here for example if I
9:00want weekday I can use weekday or I can
9:04just say DW now is it mandatory to
9:08remember all these date parts and
9:09abbreviations not really okay because if
9:13you just search MSDN you get it okay
9:17but then these are very easy to remember
9:20as well for example if you want the ear
9:22part you will just specify year if you
9:24want the month you will say month if you
9:26want the day you will say day if you
9:28want the weekday you will say weekday
9:30they will give you an integer number I
9:33mean though this number is represented
9:37in a string format it's a number it's a
9:40day number
9:41okay whereas if you want the weekday
9:45named baby we use weekday similarly you
9:48can you have several other date parts as
9:50well for example seconds milliseconds
9:52microseconds etc so depending on your
9:54requirement you choose the appropriate
9:56date path and I suggest to use the full
10:00date part rather than the abbreviation
10:03because that will be clear you know when
10:05you look at that it's more readable
10:06rather than saying dy or QQ okay so
10:13that's about date name now let's get a
10:15very simple example of using this date
10:18name function okay now in our database
10:22we have a very simple table select star
10:27from TBL employees so if you look at
10:31this table it has gone just you know
10:35three columns Heidi name and the date of
10:37birth okay just like this ID name and
10:40date of birth now what I want to do is
10:42we want to write a query which returns
10:45an output something like this I want the
10:47name of the person date of birth and the
10:49day they are born on okay are they born
10:51on a Monday Tuesday Wednesday Thursday
10:53so depending on the date of birth we'll
10:55have to calculate that and I also want
10:58the month number is it twelve month nine
11:00month eight month whatever and the month
11:03name is a December August whatever and
11:06basically the year and obviously you
11:09would have guessed it by now how to
11:11write this query we use the functions
11:13that we have just discussed and if you
11:15look at the query it's pretty simple we
11:17want the name as it is so we will say
11:19name and then the date of birth as it is
11:21we'll say date of birth so that's pretty
11:23simple
11:23next we want the day okay the weekday is
11:27it a Monday Tuesday Wednesday so I can
11:30use the date name a function pass
11:32weekday for the date part parameter and
11:35date of birth column work on date of
11:39birth from the date of birth find out
11:41what is the weekday and return that has
11:44day okay and then next we want the month
11:48number okay if you want the month number
11:51in the previous session I mean the
11:53previous slide we have seen how to use
11:55the man
11:55function that will give you the month
11:57number okay so we are basically using
12:00that month function and we are telling
12:02okay operate on date of birth column and
12:05return the month number and similarly I
12:08want the month name next so I am passing
12:12in month as the date part for the date
12:14name function and operate on date of
12:16birth and return the month name and
12:18finally I want the year so we are using
12:20the ear function which is pretty simple
12:23and we have that exact same query
12:25written here so if you look at that this
12:29is exactly the query that we have and
12:32when we execute this query you should
12:36see the output that we want on this
12:42slide you can find resources for asp.net
12:44and C sharp interview questions that's
12:46it for today thank you for listening
12:48have a great day

About this transcript

This page contains the full transcript of IsDate, Day, Month, Year and DateName DateTime functions in SQL Server Part 26 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,030 words across 283 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.