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;

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

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.

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

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:

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

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:

firstday SQL SERVER   Script to Find First Day of Current Month

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

aprilmonth MySQL   Finding First day and Last day of a MonthMySQL 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)

MySQL – How to Detect Current Time Zone Name in MySQL

world time zone map MySQL   How to Detect Current Time Zone Name in MySQL

MySQL developers and DBA often face a very simple challenge when they have to retrieve the time zone of the session which they are connected to. If you look at MySQL documentation and search engine there are many different ways to do the same. In this blog post, I will demonstrate the simple method which I use to detect Timezone of the server I am connected with the current session.

Run following script in against your MySQL server:

SELECT @@system_time_zone;

It will return the result set with the name of your Timezone. For example, I am in India and the time zone of my machine is India Standard Time hence the result will contain the same.

timezone MySQL   How to Detect Current Time Zone Name in MySQL

Let me know if you use any other method to retrieve the Timezone of the server where MySQL is installed.

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

SQL SERVER – Get Current TimeZone Name in SQL Server

A very common question we developer often encounters is what is the current Timezone of the server where SQL Server is installed. It is very easy to get current Timezone of the server where SQL Server is installed. Here is the simple script for the same.

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone

As my server is in India it will display following results.

timezonelist SQL SERVER   Get Current TimeZone Name in SQL Server

I have been using this script for a quite a while and I have no idea of the origin of this script. Is there any other way to get the Timezone for SQL Server.

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

MySQL – When to Use TIMESTAMP or DATETIME – Difference Between TIMESTAMP or DATETIME

clocktimeimage MySQL   When to Use TIMESTAMP or DATETIME   Difference Between TIMESTAMP or DATETIMEThis is a one of the most popular question, I often receive when MySQL Developers are creating a database. There are multiple datatypes which can store DateTime datatype in MySQL. The usual confusion comes up between DATETIME and TIMESTAMP.

DATETIME and TIMESTAMP – both of them can store datetime data just fine and retrieve them back, hence the question is which one to use and why?

Here are two major factor which can help you to decide which one of them, you should use for your database design.

Range

The supported range for DATETIME type is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

The supported range for TIMESTAMP type is ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

That means if you want to store date which is before the year 1970 or after the year 2038 you will need to use DATETIME.

Conversion

As per the MySQL official documentation – MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.

This means, if your application is such where you want time to stay absolutely steady with respect to GMT, you must use TIMESTAMP, or else you should use it with DATETIME.

For example, if I am using using forum, I will use TIMESTAMP as I want to capture the time when user have left comments, but if I am using an application where I have to deliver goods as per local time (and my timezone is changing), I will use DATETIME.

Summary

If you want higher range, use DATETIME and if your application is timezone independent, you should use DATETIME.

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