SQL SERVER – List the Name of the Months Between Date Ranges

Here is another interesting question, I received the other day.

“How to list the name of the months between two date ranges?”

Very interesting question. I had no script ready for it so I asked my friend who used to my co-worker earlier and he has sent me the script which is listed below.

DECLARE @StartDate  DATETIME,
@EndDate    DATETIME;
SELECT @StartDate = '20140301' -- March
,@EndDate   = '20140901'; -- September
SELECT  DATENAME(MONTH, DATEADD(MONTH, nos.monthnos, @StartDate)-1) AS MonthName
FROM    (SELECT 1 monthnos UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) nos
WHERE     nos.monthnos <= DATEDIFF(MONTH, @StartDate, @EndDate)+1;

Above script does return the name of the months between date ranges. Let me know if there is any other way to achieve the same.

Please note: This blog post is modified based on the feedback of SQL Expert Sanjay Monpara. Thank you Sanjay!

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Finding if Current Week is Odd or Even – Script

Here is an interesting question I received from my friend who is working in Bank as a DBA.

“Pinal,

We have a requirement in bank that every 2nd and 4th week we keep more cash in our bank where as we can keep less cash on other weeks. I want to write an automated script which indicates that if the current week is ODD or EVEN. Based on this information, I can write more actions in my procedures. Do you have such script which can help me?”

Very interesting question. The matter of fact, I have a script which I have been using quite a while for similar logic. The script is not written by me, but I have it with me as a resource for quite a while. Here is the script.

DECLARE @CurDate DATETIME
SET
@CurDate = GETDATE()
SELECT
WeekOfMoth = DATEPART(wk, @CurDate)
-
DATEPART(wk,DATEADD(m, DATEDIFF(M, 0, @CurDate), 0)) + 1,
CASE WHEN (DATEPART(wk, @CurDate)
-
DATEPART(wk,DATEADD(m, DATEDIFF(M, 0, @CurDate), 0)) + 1) % 2 = 1
THEN 'Odd' ELSE 'Even' END EvenOrOdd

If I run above script for today’s date 12/7/2014, it will give me following results.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Convert Seconds to Hour : Minute : Seconds Format

Here is another question I received via email.

“Hi Pinal,

I have a unique requirement. We measure time spent on any webpage in measure of seconds. I recently have to build a report over it and I did few summations based on group of web pages. Now my manager wants to convert the time, which is in seconds to the format Hour : Minute : Seconds. I researched online and found a solution on stackoverflow for converting seconds to the Minute : Seconds but could not find a solution for Hour : Minute : Seconds.

Would you please help?”

Of course the logic is very simple. Here is the script for your need.

DECLARE @TimeinSecond INT
SET
@TimeinSecond = 86399 -- Change the seconds
SELECT RIGHT('0' + CAST(@TimeinSecond / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((@TimeinSecond / 60) % 60 AS VARCHAR),2)  + ':' +
RIGHT('0' + CAST(@TimeinSecond % 60 AS VARCHAR),2)

Here is the screenshot of the resolution:

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Validating If Date is Last Day of the Year, Month or Day

Here is one more question I recently received in an email-

“Pinal, is there any ready made function which will display if the given date is the last day or the year, month or day?

For example, if a date is the last day of the Year and last day of the month, I want to display Last Day of the Year and if a date is the last date of the month and last day of the week, I want to display the last day of the week. “

Well, very interesting question and there is no such function available for the same.

However, here is the function I have written earlier for my personal use where I almost accomplish same task.

-- Example of Year
DECLARE @Day DATETIME
SET
@Day = '2014-12-31'
SELECT
CASE
WHEN CAST(@Day AS DATE) = CAST(DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@Day)+1,0))) AS DATE) THEN 'LastDayofYear'
WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Day)+1,0)) AS DATE) THEN 'LastDayofMonth'
WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(wk, DATEDIFF(wk,0,@Day),0)) AS DATE) THEN 'LastDayofWeek'
ELSE 'Day'
END
GO
-- Example of Month
DECLARE @Day DATETIME
SET
@Day = '2014-06-30'
SELECT
CASE
WHEN CAST(@Day AS DATE) = CAST(DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@Day)+1,0))) AS DATE) THEN 'LastDayofYear'
WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Day)+1,0)) AS DATE) THEN 'LastDayofMonth'
WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(wk, DATEDIFF(wk,0,@Day),0)) AS DATE) THEN 'LastDayofWeek'
ELSE 'Day'
END
GO
-- Example of Month
DECLARE @Day DATETIME
SET
@Day = '2014-05-04'
SELECT
CASE
WHEN CAST(@Day AS DATE) = CAST(DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@Day)+1,0))) AS DATE) THEN 'LastDayofYear'
WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Day)+1,0)) AS DATE) THEN 'LastDayofMonth'
WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(wk, DATEDIFF(wk,0,@Day),0)) AS DATE) THEN 'LastDayofWeek'
ELSE 'Day'
END
GO

Let me know if you know any other smarter trick and we can post it here with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Script to Find First Day of Current Month

Earlier I wrote a blog post about SQL SERVER – Query to Find First and Last Day of Current Month and it is a very popular post. In this post, I convert the datetime to Varchar and later on use it. However, SQL Expert Michael Usov has made a good point suggesting that it is not always a good idea to convert datetime to any other date format as it is quite possible that we may need it the value in the datetime format for other operation. He has suggested a very quick solution where we can get the first day of the current month with or without time value and keep them with datatype datetime.

Here is the simple script for the same.

-- first day of month
-- with time zeroed out
SELECT CAST(DATEADD(DAY,-DAY(GETDATE())+1, CAST(GETDATE() AS DATE)) AS DATETIME)
-- with time as it was
SELECT DATEADD(DAY,-DAY(GETDATE())+1, CAST(GETDATE() AS DATETIME))

Here is the resultset:

Reference: Pinal Dave (http://blog.SQLAuthority.com)

MySQL – How to Format Date in MySQL with DATE_FORMAT()

MySQL supports formatting the datetime values into a different formats using DATE_FORMAT() function. This function accepts date/datetime values as a first parameter and returns into a specific format defined as a second parameter.

Let us explore this with the following examples which are self-explanatory

Define a DATETIME variable
SET @date:='2014-06-16 14:12:49';

-- Display datetime values in YYYY-mm-dd format
SELECT date_format(@date,'%Y-%m-%d') AS formatted_date;

The result is 2014-06-16

-- Display datetime values as Long Date format
SELECT date_format(@date,'%W, %M %d,%Y') AS formatted_date;

The result is Monday, June 16,2014

-- Display datetime values as Full date format
SELECT date_format(@date,'%W, %M %d,%Y %T') AS formatted_date;

The result is Monday, June 16,2014 14:12:49

-- Display datetime values in HH:MM:SS format
SELECT date_format(@date,'%T') AS formatted_date;

The result is 14:12:49

-- Display datetime values in Month Year format
SELECT date_format(@date,'%M %Y')  AS formatted_date;

The result is June 2014

-- Display datetime values in mm-dd-yyyy format
SELECT date_format(@date,'%m-%d-%Y')  AS formatted_date;

The result is 06-16-2014

-- Display datetime values in dd-mm-yyyy format
SELECT date_format(@date,'%M %Y')  AS formatted_date;

The result is 16-06-2014

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – Finding First day and Last day of a Month

MySQL supports a lot of DATE and TIME related functions. If you want to find out last day of a month, you can make use of an inbuilt function named LAST_DAY.

SET @date:='2012-07-11';
SELECT LAST_DAY(@date) AS last_day;

The above code returns the value 2012-07-31

However, there is no inbuilt function to find out first day for a month. We will see two methods to find out the first day.

Method 1 : Use DATE_ADD and LAST_DAY functions

SET @date:='2012-07-11';
SELECT date_add(date_add(LAST_DAY(@date),interval 1 DAY),interval -1 MONTH) AS first_day;

Result is

first_day
 ----------
 2012-07-01

The logic is to find last day of a month using LAST_DAY function; Add 1 day to it using DATE_ADD function so that you will get first day of next month; Subtract 1 month from the result so that you will get first day of the current month

Method 2 : Use DATE_ADD and DAY functions

SET @date:='2012-07-11';
SELECT date_add(@date,interval -DAY(@date)+1 DAY) AS first_day;

Result is

first_day
 ----------
 2012-07-01

The logic is to find the day part of date; add 1 to it and subtract it from the date. The result is the first day of the month.

So you can effectively make use these functions to perform various datetime related logics in MySQL.

Reference: Pinal Dave (http://blog.SQLAuthority.com)