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)

About these ads

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?”

Click to Download Scripts

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

SQL SERVER – Difference Between CURRENT_TIMESTAMP and GETDATE() – CURRENT_TIMESTAMP Equivalent in SQL Server

A common question – I often get from Oracle/MySQL Professionals:

“What is the Equivalent to CURRENT_TIMESTAMP in SQL Server?”

Here is a common question I often get from SQL Server Professionals:

“What are differences between Difference Between CURRENT_TIMESTAMP and GETDATE ()?”

Very simple question but have showed up so frequently that I feel like to write about it.

Well in SQL Server GETDATE() is Equivalent to CURRENT_TIMESTAMP. However, if you use CURRENT_TIMESTAMP in your select statement it will work fine.

You can see in the above example – both of them returns the same value. Now let us go to next question regarding difference between GETDATE and CURRENT_TIMESTAMP. Well, the matter of the fact, there is no difference between them in SQL Server (Reference Link). CURRENT_TIMESTAMP is an ANSI SQL function, whereas GETDATE is T-SQL implementation of the same function. Both of them derive value from the operating system of the computer on which SQL Server instance is running.

Above discussion prompts another question – in this case, what should one use GETDATE or CURRENT_TIMESTAMP?

Well, this is indeed tricky and interesting question. I think I am very comfortable using the GETDATE () so I will go to use it but a matter of the fact there is no right or wrong answer. If you want to follow ancient saying “When in Rome, do as the Romans do”, I suggest using the GETDATE (), or continue using CURRENT_TIMESTAMP.

With that said, there is one very important property we all need to keep in mind. If you use CURRENT_TIMESTAMP while creating an object, they are automatically converted to GETDATE() and stored internally. To illustrate what I am suggesting here is the example -

Create a table using the following script

CREATE TABLE [dbo].[TestTable](
[Cold2] [datetime] NULL
)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestTable] ADD DEFAULT (CURRENT_TIMESTAMP) FOR [Cold2]
GO

Now go to SSMS and generate the script for the table and you will notice following syntax.

CREATE TABLE [dbo].[TestTable](
[Cold2] [datetime] NULL
)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestTable] ADD DEFAULT (GETDATE()) FOR [Cold2]
GO

You can notice that SQL Server have automatically converted CURRENT_TIMESTAMP to GETDATE(). I guess this gives us an idea how they behave. Now go ahead and make your choice! Do let me know which one will you use CURRENT_TIMESTAMP or GETDATE () in the comments area.

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

SQL SERVER – Fix Error: Microsoft OLE DB Provider for SQL Server error ‘80040e07′ or Microsoft SQL Native Client error ‘80040e07′

I quite often receive questions where users are looking for solution to following error:

Microsoft OLE DB Provider for SQL Server error ‘80040e07′
Syntax error converting datetime from character string.

OR

Microsoft SQL Native Client error ‘80040e07′
Syntax error converting datetime from character string.

If you have ever faced above error – I have a very simple solution for you.

http://www.pinaldave.com/bimg/error-logo.jpgThe solution is being very check date which is inserted in the datetime column. This error often comes up when application or user is attempting to enter an incorrect date into the datetime field. Here is one of the examples – one of the reader was using classing ASP Application with OLE DB provider for SQL Server. When he tried to insert following script he faced above mentioned error.

INSERT INTO TestTable (ID, MyDate)
VALUES (1, '01-Septeber-2013')

The reason for the error was simple as he had misspelled September word. Upon correction of the word, he was able to successfully insert the value and error was not there. Incorrect values or the typo’s are not the only reason for this error. There can be issues with cast or convert as well. If you try to attempt following code using SQL Native Client or in your application you will also get similar errors.

SELECT CONVERT (datetime, '01-Septeber-2013', 112)

The reason here is very simple, any conversion attempt or any other kind of operation on incorrect date/time string can lead to the above error. If you not using embeded dynamic code in your application language but using attempting similar operation on incorrect datetime string you will get following error.

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

Remember: Check your values of the string when you are attempting to convert them to string – either there can be incorrect values or they may be incorrectly formatted.

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

SQL SERVER – Find Weekend and Weekdays from Datetime in SQL Server 2012

Yesterday we had very first SQL Bangalore User Group meeting and I was asked following question right after the session.

“How do we know if today is a weekend or weekday using SQL Server Functions?”

Well, I assume most of us are using SQL Server 2012 so I will suggest following solution. I am using SQL Server 2012’s CHOOSE function. It is

SELECT GETDATE() Today,
DATENAME(dw, GETDATE()) DayofWeek,
CHOOSE(DATEPART(dw, GETDATE()), 'WEEKEND','Weekday',
'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay
GO

You can use the choose function on table as well. Here is the quick example of the same.

USE AdventureWorks2012
GO
SELECT A.ModifiedDate,
DATENAME(dw, A.ModifiedDate) DayofWeek,
CHOOSE(DATEPART(dw, A.ModifiedDate), 'WEEKEND','Weekday',
'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay
FROM [Person].[Address] A
GO

If you are using an earlier version of the SQL Server you can use a CASE statement instead of CHOOSE function.

Please read my earlier article which discusses CHOOSE function and CASE statements. Logical Function – CHOOSE() – A Quick Introduction

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