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)

About these ads

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

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)