# 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.

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)

# 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.

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.

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

This 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)

# SQL SERVER – Adding Column Defaulting to Current Datetime in Table

Presenting a technical session is a greatest experience one can have and I enjoy doing the same. While I write this blog post, I am presenting at Great Indian Developer Summit in India. The event is a grand success and I am having a great time at this event. One of the questions which I often receive is how do one can add the column to existing table which will be auto-populated with the current datetime when the original row is inserted. There is indeed a simple solution to achieve this goal. One has to just create table with default value as a current datetime.

In following example we will first create a sample table and later we will add a column which will be defaulted to the current date time when any new record is inserted. The only drawback of this method is that if there is any existing row in your table it will be automatically have the current date time when the column is created. Honestly I do not see any solution to this issue as this is related to design of the database. If you know what was the datetime when rows were created you can update those rows with those value otherwise, just have any values stored there.

Let us see our solution. Let us first create a table which does not have column with current datetime. In our case we will assume that there are only two rows in the table.

```USE tempdb GO -- Create Table CREATE TABLE TestTable (ID INT, Col1 VARCHAR(100)); -- Insert Values INSERT INTO TestTable (ID, Col1) SELECT 1, 'First' UNION ALL SELECT 2, 'Second'; -- Select from table SELECT * FROM TestTable GO```

Now let us add a column to this table with default value as a current datetime. You will notice that the two rows which are inserted into the table have current datetime.

```-- Add Column with Default Current Date Time ALTER TABLE TestTable ADD DateInserted DATETIME NOT NULL DEFAULT (GETDATE()); -- Select from table SELECT * FROM TestTable GO```

As a third step let us enter the a new row. Make sure that you do not insert any value in the newly created column where you have default value as a current date time.

```-- Now Insert New Rows INSERT INTO TestTable (ID, Col1) SELECT 3, 'Third'; INSERT INTO TestTable (ID, Col1) SELECT 4, 'Fourth'; GO -- Select from table SELECT * FROM TestTable GO```

You will notice in the result set that the new column will contain current date time of the row created. This way you can get the value when the row was created.

Now you can clean up the resultset.

```-- Clean up DROP TABLE TestTable GO```

Here is the question back to you – “It is simple to create a column where we have default daytime value to know when the row was created. Is there any way to know when the row was updated without explicitly updating any column with datetime?”