tag:blogger.com,1999:blog-6082652835152798567.post5303040561001999301..comments2024-03-19T00:55:43.409-07:00Comments on Sql server, .net and c# video tutorial: DatePart, DateAdd and DateDiff functions in SQL Server - Part 27Unknownnoreply@blogger.comBlogger13125tag:blogger.com,1999:blog-6082652835152798567.post-90915179941373062902018-08-21T22:31:42.463-07:002018-08-21T22:31:42.463-07:00Hi I have to calculate the financial year .. which...Hi I have to calculate the financial year .. which start from 1 July and ends with 30 June ..how to calculate..<br />Anonymoushttps://www.blogger.com/profile/00001753296146543466noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-44027320658597047172018-03-19T11:55:37.384-07:002018-03-19T11:55:37.384-07:00Hi, I am getting this error "Arithmetic overf...Hi, I am getting this error "Arithmetic overflow error converting expression to data type nvarchar."<br />I did exactly what this video says.<br />Thank youAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-55180140344916639512018-02-20T21:31:04.165-08:002018-02-20T21:31:04.165-08:00select ISDATE('sunanda');--0
select ISDATE...select ISDATE('sunanda');--0<br />select ISDATE('23:52:41.8962531');--0<br />select ISDATE(GETDATE());--1<br />select ISDATE('2018-12-15');--1<br />select ISDATE('12-15-2018');--1<br />select ISDATE('12-15-2018 23:52:41.896');--1<br />select ISDATE('12-15-2018 23:52:41.8964124');--0<br />select ISDATE('12-15-2018 23:52:41.8964124 +12:00');--0<br /><br />--NOTE time,datetime2 & datetimeoffset it retuens ZERO<br /><br />---------------------------------------------------------<br />--retuens DAY or MONTH or YEAR number of perticular date<br />--DAY()<br /><br />select DAY('1996-04-15') --DAY 15<br />select MONTH('1996-04-15') --DAY 04<br />select YEAR('1996-04-15') --DAY 1996<br />select DAY(GETDATE()) <br />select MONTH(GETDATE()) <br />select YEAR(GETDATE())<br /><br />--------------------------------------------<br />--returns the string that part of pericular string<br />--DATENAME(daypart,date)<br /><br />select DATENAME(Weekday,Getdate())<br />select DATENAME(month,Getdate())<br />select DATENAME(year,Getdate())<br />select DATENAME(hour,Getdate())<br />select DATENAME(minute,Getdate())<br />select DATENAME(second,Getdate())<br />select DATENAME(millisecond,Getdate())<br />select DATENAME(microsecond,Getdate())<br />select DATENAME(nanosecond,Getdate())<br /><br />select DATENAME(QUARTER,Getdate())<br />select DATENAME(QUARTER,'03-25-2018')<br />select DATENAME(QUARTER,'06-25-2018')<br />select DATENAME(QUARTER,'09-25-2018')<br />select DATENAME(QUARTER,'12-25-2018')<br /><br />select DATENAME(week,'01-01-2018')<br />select DATENAME(week,'01-31-2018')<br />select DATENAME(week,'02-01-2018')<br />select DATENAME(week,'02-21-2018')<br />select DATENAME(week,'02-28-2018')<br />select DATENAME(week,'12-31-2018')<br /><br />select DATENAME(DAYOFYEAR,'01-01-2018')<br />select DATENAME(DAYOFYEAR,'12-31-2018')<br /><br />select DATENAME(TZOFFSET,'12-31-2018')<br />-----------------------------------------<br />--returns the integer that part of pericular string<br />--DATEPART(daypart,date)<br /><br />select DATEPART(Weekday,Getdate())<br />select DATEPART(month,Getdate())<br />select DATEPART(year,Getdate())<br />select DATEPART(hour,Getdate())<br />select DATEPART(minute,Getdate())<br />select DATEPART(second,Getdate())<br />select DATEPART(millisecond,Getdate())<br />select DATEPART(microsecond,Getdate())<br />select DATEPART(nanosecond,Getdate())<br /><br />select DATEPART(QUARTER,Getdate())<br />select DATEPART(QUARTER,'03-25-2018')<br />select DATEPART(QUARTER,'06-25-2018')<br />select DATEPART(QUARTER,'09-25-2018')<br />select DATEPART(QUARTER,'12-25-2018')<br /><br />select DATEPART(week,'01-01-2018')<br />select DATEPART(week,'01-31-2018')<br />select DATEPART(week,'02-01-2018')<br />select DATEPART(week,'02-21-2018')<br />select DATEPART(week,'02-28-2018')<br />select DATEPART(week,'12-31-2018')<br /><br />select DATEPART(DAYOFYEAR,'01-01-2018')<br />select DATEPART(DAYOFYEAR,'12-31-2018')<br /><br />select DATEPART(TZOFFSET,'12-31-2018')<br /><br /><br />---------------------------------------<br />--comaprision of DatePart & Date Name<br /><br />select DATENAME(month,Getdate())<br />select DATENAME(year,Getdate())<br />select DATENAME(weekday,Getdate())<br /><br />select DATEPART(month,Getdate())<br />select DATEPART(year,Getdate())<br />select DATEPART(weekday,Getdate())<br /><br />-------------------------------------------<br />--DateAdd(datepart,NumberTOadd,Add)<br /><br />select DateAdd(month,10,'2018-02-21')--it will add 10 mnths 2018-12-21 00:00:00.000<br />select DateAdd(year,10,'2018-02-21')--it will add 10 YEARS 2028-02-21 00:00:00.000<br />select DateAdd(weekday,10,'2018-02-21')--it will add 10 Days 2018-03-03 00:00:00.000<br /><br />select DateAdd(month,-10,'2018-02-21')--it will remove 10 mnths 2017-04-21 00:00:00.000<br />select DateAdd(year,-10,'2018-02-21')--it will remove 10 YEARS 2008-02-21 00:00:00.000<br />select DateAdd(weekday,-10,'2018-02-21')--it will remove 10 Days 2018-02-11 00:00:00.000<br />Anonymoushttps://www.blogger.com/profile/05622680059022579599noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-38635809424225854432017-07-28T05:35:08.912-07:002017-07-28T05:35:08.912-07:00how do i can take this query into a trigger that c...how do i can take this query into a trigger that check the age and rollback when it's not in the range?Anonymoushttps://www.blogger.com/profile/16949032600218549453noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-40878486698201558532017-04-08T03:24:33.557-07:002017-04-08T03:24:33.557-07:00Pls check, getting days count in your function.
S...Pls check, getting days count in your function.<br /><br />Select @day = DATEDIFF(Day, @tempdate, @DOB)<br />in above code instead of @DOB you can use GETDATE()<br /><br /><br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-57680174652762885412017-03-11T05:29:01.104-08:002017-03-11T05:29:01.104-08:00I am getting this error.. Can any one guide me how...I am getting this error.. Can any one guide me how to resolve this???<br /><br />Msg 8115, Level 16, State 2, Line 1<br />Arithmetic overflow error converting expression to data type nvarchar.<br /><br />Create Table Employee<br />(Id int NOT NULL primary key,<br />Name nvarchar(50),<br />DOB datetime<br />)<br /><br />insert into Employee values ('1', 'Atique', '1986-11-14 08:26:00.000')<br />insert into Employee values ('2', 'Imran', '1984-10-10 03:32:00.000')<br />insert into Employee values ('3', 'Hassan', '1996-07-26 08:26:00.000')<br />insert into Employee values ('4', 'Khawar', '1990-11-02 03:32:00.000')<br />insert into Employee values ('5', 'Asad', '1991-01-03 03:32:00.000')<br />insert into Employee values ('6', 834783, '1990-11-02 03:32:00.000')<br /><br /><br /><br />select * from Employee<br /><br />Create Function ComputeEmpolyeesAge (@DOB DateTime)<br />Returns NVARCHAR(50)<br />AS<br />BEGIN<br />Declare @tempdate Datetime, @years Int, @month Int, @day Int<br />Select @tempdate = @DOB<br /><br />Select @years = DATEDIFF (YEAR, @tempdate, GETDATE()) - Case When<br />(Month (@DOB) > (GETDATE())) OR MONTH(@DOB) = Month(GETDATE()) AND DAY(@DOB) > DAY(GETDATE()) THEN 1 ELSE 0 END<br />Select @tempdate = DATEADD (YEAR, @years, @tempdate)<br /><br />Select @month =DATEDIFF (Month, @tempdate, GETDATE()) - CASE WHEN<br />Day(@DOB) > Day(GetDate()) THEN 1 ELSE 0 END<br />Select @tempdate = DATEADD(Month,@month, @tempdate)<br /><br />Select @day = DATEDIFF(Day, @tempdate, @DOB)<br /><br />Declare @Age Nvarchar(50)<br />Set @Age = Cast (@years as Nvarchar(4)) + 'Years' + cast (@month as nvarchar (2)) + 'Montbs' + Cast(@day as nvarchar(2)) + 'Days Old'<br />Return @Age <br />END<br />Select Name, DOB, dbo.ComputeEmpolyeesAge(convert (nvarchar,'1991-12-23 10:14:00.067')) as Age<br />from Employee<br /><br />Jasuz1889https://www.blogger.com/profile/03870073330307828252noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-72029491246110140362016-08-19T07:32:50.456-07:002016-08-19T07:32:50.456-07:00When I am executing function dbo.fnComputeAge in ...When I am executing function dbo.fnComputeAge in the end it is showing an error as 'Arithmetic overflow error converting expression to data type nvarchar.'<br />Please tell me whats wrong with this<br />Anonymoushttps://www.blogger.com/profile/01465996371415053868noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-81221032735980433272016-07-23T02:05:03.460-07:002016-07-23T02:05:03.460-07:00Your video tutorials are of great help to any seri...Your video tutorials are of great help to any serious developer,It helps to clear concepts with clarity.<br />Thanks for all the effort you have put up to group the tutorials in logical order.Shrishail Nagathanhttps://www.blogger.com/profile/05137465675475335067noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-41933585707247420142015-11-06T19:01:54.906-08:002015-11-06T19:01:54.906-08:00If the videos are not vicible properly, increse re...If the videos are not vicible properly, increse resolution of the video (right bottom cornor-->gear like symbol to 360/480)KDV PRASADhttps://www.blogger.com/profile/09545823686070051374noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-40304101511145015582015-07-03T13:03:36.202-07:002015-07-03T13:03:36.202-07:00listen brother GETDATE() is always grater then to ...listen brother GETDATE() is always grater then to DOB<br />if DOB is grater then GETDATE() means <br />that person is not born <br />that is a feature thing<br />if DOB and GETDATE() are same then datediff() gives zero<br />and IF DOB is grater then GETDATE() means age goes -ve <br />world knows age, energy,mass like these thing never have -ve valueAnonymoushttps://www.blogger.com/profile/17082420144105917859noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-8041057969465006602014-03-24T02:12:35.175-07:002014-03-24T02:12:35.175-07:00if the persons DOB is greater than GETDATE() value...if the persons DOB is greater than GETDATE() value then the above function will fail<br />you need to check using if condition<br /><br />wasimAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-68086413288456232442014-03-23T11:03:08.874-07:002014-03-23T11:03:08.874-07:00Hello sir,thanks a lot for the videos....but sorry...Hello sir,thanks a lot for the videos....but sorry to say that some vedeos are not visible that means i am not able to see the query i.e. all are grayed outRamakanta Beherahttps://www.blogger.com/profile/13345936538415610876noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-85288079051550775412013-11-21T20:25:20.708-08:002013-11-21T20:25:20.708-08:00Sir very long back when i visited your blog, I am ...Sir very long back when i visited your blog, I am not sure, But i felt like i have seen javascript vedio tutorials.. If I am not wrong, please upload it again sir.... Anonymousnoreply@blogger.com