Every time I wrote a blog, I tend to get back to most of them via the search. Most of you ask me questions and I do few simple search back to make sure I am able to address them. Yesterday as I was searching for an interesting question, Now back to the question that got me there, one of a friend said he was asked in an interview on how to efficiently find the number of days in a given year.
The question was interesting but what baffled me was – do people still ask such questions? It was a challenge that I wanted to share with you. I wanted to take the challenge and immediately got to my SQL Server machine to solve. The instinctive behavior was to use some basic methods that involved DATEADD and DATEPART options.
The first solution that I got was:
--This method will work on SQL server 2005 and above.
DECLARE @year AS INT
SET @year=2012
SELECT DATEPART(dy,(DATEADD(YEAR,@year-1899,0)-1))
AS [TOTAL NO OF DAYS]
GO
Immediately I sent the above option. Then he quizzed me asking, can you use some of the New Date time functions that were introduced with SQL Server 2012. Now, I was not prepared for the googly that came my way. But after getting my heads around it, I came up with the below query.
--This technique will work in SQL Server 2012 and above.
DECLARE @year AS INT
SET @year=2012
SELECT DATEPART(dy,DATEFROMPARTS(@Year,12,31))
AS [TOTAL NO OF DAYS]
Woot !!! That was not simple as I had to search my blog for ideas.
Quiz: Can you come up with some solution which will have lesser number of functions involved? Can you use some of the new SQL Server 2012 Date functions in your solution? Let me know via comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
17 Comments. Leave new
I am just beginning to learn programming with Transact_SQL so needed to learn how to create a function before submitting my answer with calls no system functions at all.
This is my answer
CREATE FUNCTION [dbo].[udf_IsLeapYear] ( @pccyy_year INT )
RETURNS INT
AS
BEGIN
DECLARE @rtLeapYear INT
SET @rtLeapYear = CASE WHEN (@pccyy_year % 4) = 0
AND
((@pccyy_year % 100) != 0)
OR
((@pccyy_year % 400) = 0)
THEN 0
ELSE 1 END
RETURN @rtLeapYear
END
GO
SELECT dbo.udf_IsLeapYear(2012) IsLeapYear
GO
If you recognise the code it is almost the code in your example
SEPTEMBER 8, 2007 BY PINAL DAVE
SQL SERVER – UDF – User Defined Function – Get Number of Days in Month
I think on rereading the question you meant us to use a function/s from SQL Server 2012 so my answer is probably not what you wanted.
More important than anything a million thanks for producing this blog, the answers you give are clear and concise.
Again thank you
I apologise to all I somehow got leap year into my head
No problems Michael. We always learn from each other. Thanks for taking time.
Here is another method of doing this
Thanks for sharing
Here is another way that works, still 2 functions though….
declare @year_to_test char(4) = 2012
select 368 – (datepart(month, dateadd(day, 1, @year_to_test + ‘-02-28’)))
You don’t really need any functions per se. This should work for at least thru 2099. You could further adjust it to work for any (reasonable) year.
DECLARE @year AS INT
SET @year=2012
SELECT 365 + CASE WHEN @year % 4 = 0 THEN 1 ELSE 0 END
Not entirely correct, if you look at some of the linked leap year posts you will see there are exceptions to the %4
ScottPletcher – Your leap year calculation logic is incorrect. There are few more cases where year is divisible by 4 and still not a leap year.
You just can not find modulus using 4. Refer this post for additional conditions
This is Error when I am running SQL Command on destination Server
OLE DB provider “SQLNCLI10” for linked server “(null)” returned message “Login timeout expired”.
OLE DB provider “SQLNCLI10” for linked server “(null)” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
The above error i am getting while executing storedprocedure against msdb database..please give suggestion..especcizlly in sql server 2012…same working fine in 2005 and 2008
It’s not an issue with version. The error is about linked server connectivity.
Hi pinal Thanks for your replay..i am getting this issue while execting stored procedure aginst msdb database in sql server 2012..same i executed in sql server 2008..it’s working fine..the flle acript as follows..
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[uspEmailSQLServerHealth] Script Date: 03/30/2015 17:18:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspEmailSQLServerHealth]
(
@ServerIP VARCHAR(100), — SQL Server 2005 Database Server IP Address
@Project VARCHAR(100), — Name of project or cleint
@Recepients VARCHAR(2000), — Recepient(s) of this email (; separated in case of multiple recepients).
@MailProfile VARCHAR(100), — Mail profile name which exists on the target database server
@Owner VARCHAR(200) — Owner, basically name/email of the DBA responsible for the server
)
/*
exec EmailSQLServerHealth ‘10.10.10.10’, ‘MYProject’, ‘myself@mycompany.com’, ‘TestMailProfile’, ‘My Self’
*/
AS
BEGIN
SET NOCOUNT ON
/* Drop all the temp tables(not necessary at all as local temp tables get dropped as soon as session is released,
however, good to follow this practice). */
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = ‘#jobs_status’)
BEGIN
DROP TABLE #jobs_status
END
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = ‘#diskspace’)
BEGIN
DROP TABLE #diskspace
END
IF EXISTS (SELECT NAME FROM sysobjects WHERE name = ‘#url’)
BEGIN
DROP TABLE #url
END
IF EXISTS (SELECT NAME FROM sysobjects WHERE name = ‘#dirpaths’)
BEGIN
DROP TABLE #dirpaths
END
— Create the temp tables which will be used to hold the data.
CREATE TABLE #url
(
idd INT IDENTITY (1,1),
url VARCHAR(1000)
)
CREATE TABLE #dirpaths
(
files VARCHAR(2000)
)
CREATE TABLE #diskspace
(
drive VARCHAR(200),
diskspace INT
)
— This table will hold data from sp_help_job (System sp in MSDB database)
————–CREATE TABLE #jobs_status
————–(
————– job_id UNIQUEIDENTIFIER,
————– originating_server NVARCHAR(30),
————– name SYSNAME,
————– enabled TINYINT,
————– description NVARCHAR(512),
————– start_step_id INT,
————– category SYSNAME,
————– owner SYSNAME,
————– notify_level_eventlog INT,
————– notify_level_email INT,
————– notify_level_netsend INT,
————– notify_level_page INT,
————– notify_email_operator SYSNAME,
————– notify_netsend_operator SYSNAME,
————– notify_page_operator SYSNAME,
————– delete_level INT,
————– date_created DATETIME,
————– date_modified DATETIME,
————– version_number INT,
————– last_run_date INT,
————– last_run_time INT,
————– last_run_outcome INT,
————– next_run_date INT,
————– next_run_time INT,
————– next_run_schedule_id INT,
————– current_execution_status INT,
————– current_execution_step SYSNAME,
————– current_retry_attempt INT,
————– has_step INT,
————– has_schedule INT,
————– has_target INT,
————– type INT
————–)
— To insert data in couple of temp tables created above.
INSERT #diskspace(drive, diskspace) EXEC xp_fixeddrives
—-SET @SQL = insert into #jobs_status
—- EXEC(@SQL)
—-exec sp_serveroption ‘BDOLOWISTRAIN3’, ‘data access’, ‘true’
—-select * INTO #jobs_status FROM OPENQUERY( BDOLOWISTRAIN3, ‘ EXEC msdb.dbo.sp_help_job ‘)
—-INSERT #jobs_status EXEC master.dbo.xp_sqlagent_enum_jobs
SELECT * INTO #jobs_status
FROM OPENROWSET(‘sqloledb’, ‘server=(local);trusted_connection=yes’
, ‘set fmtonly off exec msdb.dbo.sp_help_job’)
— Variable declaration
DECLARE @TableHTML VARCHAR(MAX),
@StrSubject VARCHAR(100),
@Oriserver VARCHAR(100),
@Version VARCHAR(250),
@Edition VARCHAR(100),
@ISClustered VARCHAR(100),
@SP VARCHAR(100),
@ServerCollation VARCHAR(100),
@SingleUser VARCHAR(5),
@LicenseType VARCHAR(100),
@StartDate DATETIME,
@EndDate DATETIME,
@Cnt int,
@URL varchar(1000),
@Str varchar(1000)
— Variable Assignment
SELECT @Version = @@version
SELECT @Edition = CONVERT(VARCHAR(100), serverproperty(‘Edition’))
SELECT @StartDate = CAST(CONVERT(VARCHAR(4), DATEPART(yyyy, GETDATE())) + ‘-‘ + CONVERT(VARCHAR(2), DATEPART(mm, GETDATE())) + ‘-01’ AS DATETIME)
SELECT @StartDate = @StartDate – 1
SELECT @EndDate = CAST(CONVERT(VARCHAR(5),DATEPART(yyyy, GETDATE() + 1)) + ‘-‘ + CONVERT(VARCHAR(2),DATEPART(mm, GETDATE() + 1)) + ‘-‘ + CONVERT(VARCHAR(2), DATEPART(dd, GETDATE() + 1)) AS DATETIME)
SET @Cnt = 0
IF serverproperty(‘IsClustered’) = 0
BEGIN
SELECT @ISClustered = ‘No’
END
ELSE
BEGIN
SELECT @ISClustered = ‘YES’
END
SELECT @SP = CONVERT(VARCHAR(100), SERVERPROPERTY (‘productlevel’))
SELECT @ServerCollation = CONVERT(VARCHAR(100), SERVERPROPERTY (‘Collation’))
SELECT @LicenseType = CONVERT(VARCHAR(100), SERVERPROPERTY (‘LicenseType’))
SELECT @SingleUser = CASE SERVERPROPERTY (‘IsSingleUser’)
WHEN 1 THEN ‘Yes’
WHEN 0 THEN ‘No’
ELSE
‘null’ END
SELECT @OriServer = CONVERT(VARCHAR(50), SERVERPROPERTY(‘servername’))
SELECT @strSubject = ‘Database Server Health Checks (‘+ CONVERT(VARCHAR(50), SERVERPROPERTY(‘servername’)) + ‘)’
/*
Along with refrences to SQL Server System objects, You will also see lots of HTML code however do not worry,
*/
SET @TableHTML =
‘Server Info
Server IP
Server Name
Project/Client
‘+@ServerIP+’
‘ + @OriServer +’
‘+@Project+’
Version
Edition
Service Pack
Collation
LicenseType
SingleUser
Clustered
‘+@version +’
‘+@edition+’
‘+@SP+’
‘+@ServerCollation+’
‘+@LicenseType+’
‘+@SingleUser+’
‘+@isclustered+’
‘ +
‘Job Status
Job Name
Enabled
Last Run
Category
Last Run Date
Execution Time (Mi)
‘
SELECT
@TableHTML = @TableHTML + ” +
ISNULL(CONVERT(VARCHAR(100), A.name), ”) +” +
CASE enabled
WHEN 0 THEN ‘False‘
WHEN 1 THEN ‘True’
ELSE ‘Unknown’
END +
CASE last_run_outcome
WHEN 0 THEN ‘
<a href="mailto:nauitsc@chevron.com?subject=Job failure – ' + @Oriserver + '(' + @ServerIP + ') '+ CONVERT(VARCHAR(15), GETDATE(), 101) +'&cc=sjvdbas@chevron.com&body = SD please log this call to DB support,' + '%0A %0A' + '<> Job Failed on ‘ + @OriServer + ‘(‘ + @ServerIP + ‘)’+ ‘.’ +’%0A%0A Regards,’+'”>Failed‘
WHEN 1 THEN ‘Success’
WHEN 3 THEN ‘Cancelled’
WHEN 5 THEN ‘Unknown’
ELSE ‘Other’
END +
” + ISNULL(CONVERT(VARCHAR(100), A.category),”) + ” +
” + ISNULL(CONVERT(VARCHAR(50), A.last_run_date),”) + ” +
” + ISNULL(CONVERT(VARCHAR(50), X.execution_time_minutes),”) +’ ‘
FROM
#jobs_status A
inner join (
select
A.job_id,
datediff(mi, A.last_executed_step_date, A.stop_execution_date) execution_time_minutes
from
msdb..sysjobactivity A
inner join (
select
max(session_id) sessionid,
job_id
from
msdb..sysjobactivity
group by
job_id
) B on a.job_id = B.job_id and a.session_id = b.sessionid
inner join (
select
distinct name,
job_id
from
msdb..sysjobs
) C on A.job_id = c.job_id
) X on A.job_id = X.job_id
ORDER BY
last_run_date DESC
SELECT
@TableHTML = @TableHTML + ”
——–SELECT
——– @TableHTML = @TableHTML + ‘
——–
——–
——–
——–
——–
——– ‘
SELECT
@TableHTML = @TableHTML +
‘Databases
Name
CreatedDate
DB Size(GB)
State
Recovery Model
‘
select
@TableHTML = @TableHTML +
” + ISNULL(name, ”) +” +
” + CONVERT(VARCHAR(2), DATEPART(dd, create_date)) + ‘-‘ + CONVERT(VARCHAR(3),DATENAME(mm,create_date)) + ‘-‘ + CONVERT(VARCHAR(4),DATEPART(yy,create_date)) +” +
” + ISNULL(CONVERT(VARCHAR(10), AA.[Total Size GB]), ”) +” +
” + ISNULL(state_desc, ”) +” +
” + ISNULL(recovery_model_desc, ”) +”
from
sys.databases MST
inner join (select b.name [LOG_DBNAME],
CONVERT(DECIMAL(10,2),sum(CONVERT(DECIMAL(15,2),(a.size * 8)) /1024)/1024) [Total Size GB]
from sys.sysaltfiles A
inner join sys.databases B on A.dbid = B.database_id
group by b.name)AA on AA.[LOG_DBNAME] = MST.name
order by
MST.name
SELECT
@TableHTML = @TableHTML + ”
SELECT
@TableHTML = @TableHTML +
‘Disk Stats
Disk
Free Space (GB)
‘
SELECT
@TableHTML = @TableHTML +
” + ISNULL(CONVERT(VARCHAR(100), drive), ”) +” +
” + ISNULL(CONVERT(VARCHAR(100), ISNULL(CAST(CAST(diskspace AS DECIMAL(10,2))/1024 AS DECIMAL(10,2)), 0)),”) +”
FROM
#diskspace
SELECT @TableHTML = @TableHTML + ”
— Code for SQL Server Database Backup Stats
SELECT
@TableHTML = @TableHTML +
‘SQL SERVER Database Backup Stats
Date
Database
File Name
Type
Start Time
End Time
Size(GB)
‘
SELECT
@TableHTML = @TableHTML +
‘
‘ + ISNULL(CONVERT(VARCHAR(2), DATEPART(dd,MST.backup_start_date)) + ‘-‘ + CONVERT(VARCHAR(3),DATENAME(mm, MST.backup_start_date)) + ‘-‘ + CONVERT(VARCHAR(4), DATEPART(yyyy, MST.backup_start_date)),”) +” +
” + ISNULL(CONVERT(VARCHAR(100), MST.database_name), ”) +” +
” + ISNULL(CONVERT(VARCHAR(100), MST.name), ”) +” +
CASE Type
WHEN ‘D’ THEN ” + ‘Full’ +”
WHEN ‘I’ THEN ” + ‘Differential’ +”
WHEN ‘L’ THEN ” + ‘Log’ +”
WHEN ‘F’ THEN ” + ‘File or Filegroup’ +”
WHEN ‘G’ THEN ” + ‘File Differential’ +”
WHEN ‘P’ THEN ” + ‘Partial’ +”
WHEN ‘Q’ THEN ” + ‘Partial Differential’ +”
ELSE ” + ‘Unknown’ +”
END +
” + ISNULL(CONVERT(VARCHAR(50), MST.backup_start_date), ”) +” +
” + ISNULL(CONVERT(VARCHAR(50), MST.backup_finish_date), ”) +” +
” + ISNULL(CONVERT(VARCHAR(10), CAST((MST.backup_size/1024)/1024/1024 AS DECIMAL(10,2))), ”) +” +
”
FROM
backupset MST
WHERE
MST.backup_start_date BETWEEN @StartDate AND @EndDate
ORDER BY
MST.backup_start_date DESC
SELECT @TableHTML = @TableHTML + ”
— Code for physical database backup file present on disk
INSERT #url
SELECT DISTINCT
SUBSTRING(BMF.physical_device_name, 1, len(BMF.physical_device_name) – CHARINDEX(‘\’, REVERSE(BMF.physical_device_name), 0))
from
backupset MST
inner join backupmediafamily BMF ON BMF.media_set_id = MST.media_set_id
where
MST.backup_start_date BETWEEN @startdate AND @enddate
select @Cnt = COUNT(*) FROM #url
WHILE @Cnt >0
BEGIN
SELECT @URL = url FROM #url WHERE idd = @Cnt
SELECT @Str = ‘EXEC master.dbo.xp_cmdshell ”dir “‘ + @URL +'” /B/O:D”’
INSERT #dirpaths SELECT ‘PATH: ‘ + @URL
INSERT #dirpaths
EXEC (@Str)
INSERT #dirpaths values(”)
SET @Cnt = @Cnt – 1
end
DELETE FROM #dirpaths WHERE files IS NULL
select
@TableHTML = @TableHTML +
‘Physical Backup Files
Physical Files
‘
SELECT
@TableHTML = @TableHTML + ” +
CASE SUBSTRING(files, 1, 5)
WHEN ‘PATH:’ THEN ‘‘ + files + ‘‘
ELSE
” + files + ”
END +
”
FROM
#dirpaths
SELECT
@TableHTML = @TableHTML + ” +
‘
Server Owner: ‘+@owner+’
Thanks
and Regards,
DB
Support Team
‘
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfile,
@recipients=@Recepients,
@subject = @strSubject,
@body = @TableHTML,
@body_format = ‘HTML’ ;
SET NOCOUNT OFF
END
GO
Calculate Days in given year
Declare @Year INT=2016
select DateDiff(Day, ’01-01-‘ +Convert(Varchar,@Year), ’12-31-‘ + CONVERT(Varchar, @Year) )+1
DECLARE @year AS VARCHAR(16)
SET @year=2012
SET @year=’02/29/’+@year
SELECT IIF(ISDATE(@year)=1,366,365)
DECLARE @year AS VARCHAR(16)
SET @year=2012
SET @year=’02/29/’+@year
SELECT IIF(ISDATE(@year)=1,366,365) as [days]
DECLARE @year AS INT
SET @year=2012
select 365 + (datediff(d,datefromparts(@year,2,28), datefromparts(@year,3,1)) – 1)