May 16 2016

MySQL week of the month

Tagged Under : ,

Normally if want display week of certain of the month, we can using WEEK() function. But WEEK() function only return you week of the year.

But now, we want display WEEK of the month no week of the year. that means first, second, third, fourth and fifth week of the month.

Below example is the SQL to display the week number of the month:
SELECT (WEEK(created_date,5) - WEEK(DATE_SUB(created_date, INTERVAL DAYOFMONTH(created_date)-1 DAY),5)+1) as week
The created_date column must be DATE or DATETIME type.

June 04 2014

Adding number of days to Dates in MySQL

Tagged Under : ,

One of my application need update the days when it have set the days inside the records. It was because sometimes user need extend the expired date to following number of days.

With the SQL statement it would be simple to complete the task.
UPDATE table 
SET expired_date = DATE_ADD(expired,INTERVAL 7 DAY) 
WHERE id = 1;
The above example only work on DATE or DATETIME field types. Remember it was day not days

June 04 2014

multiple counts with one SQL Query

Tagged Under : ,

It have few ways to get multiple counts with one SQL Query. But, it also will return more then one row records to user, and also the total count wouldn’t appear if it no inside the records.

In the same time, user need did few times loop and if else conditions to get the results. It will slow down the application performance and give developer more job to completed the job.

In here, I have a better way to process multiple counts with one SQL Query and it just return one row record only.

Let’s see below example how the SQL look like:

January 21 2014

SQL Server Date Formats

Tagged Under : ,

How to format datetime value or column into a specific date format. Is the most frequently asked question by the newbie. Below table is summary of the different date formats that come from SQL Server with the CONVERT function.

Please note that the output of these date formats are VARCHAR data type, not DATETIME data type. With this in mind, any date comparisons performed after the DATETIME value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.

December 22 2013

MySQL check for duplicates before inserting

Tagged Under : ,

Tables or result sets sometimes contain duplicate records. Sometimes, it is allowed but sometimes it is required to stop duplicate records. Sometimes, it is required to identify duplicate records and remove them from the table. This chapter will describe how to prevent duplicate records occurring in a table and how to remove already existing duplicate records.

You are allow use PRIMARY KEY or UNIQUE Index on a table with appropriate fields to stop duplicate records. Let’s create an example table with id and name as Primary Key:
  `name` varchar(100) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`id`, `name`),

December 20 2013

MySQL ordering by specific field values

Tagged Under : ,

There may be times when a specific order is required in a SQL query which cannot be done using either ASC or DESC or using a special sort field. MySQL has a ORDER BY FIELD function which can be used to do this.

Example Data
The example data in this post uses as below. This is a somewhat simple table but it can be used to illustrate the point in this post quite well.
CREATE TABLE fruits (`id` int, `name` varchar(50));
INSERT INTO fruits (`id`, `name`)
VALUES (1, 'Apple'),
       (2, 'Durian'),
       (3, 'Banana'),
       (4, 'Lemon'),
       (5, 'Pear'),
       (6, 'Star fruit'),
       (7, 'Strawberry'),
       (8, 'Orange');

October 26 2013

Mysql last day of the month

Tagged Under : ,

Because sometimes need to know last day of the previous month, and I was using procedure/routine to completed the query. That’s the reason I can’t use others programming language to do it. All the thing must finish in MySQL server only.

And very lucky, MySQL have “last_day()” function to get last day of the month.

Below example are showing how to use it.

October 26 2013

Can’t drop database ‘dbname’; database doesn’t exist and database exists

Tagged Under : ,

Recently I reinstall MySQL Database because it suddenly cannot start and it crash. After I successful reinstall, I found an extra database in MySQL Server.

When I tried to drop the database, I received the following error:
“Can’t drop database ‘dbname’; database doesn’t exist”
And when I tried to create a new database with same name, it told me:
“Can’t create database ‘dbname’; database exists”
That just didn’t make any sense! And the PHPMyAdmin application still listed the database in the Catalog list.

September 16 2013

Retrieving the last record in each group

Tagged Under : ,

Sometimes we keep all the records/logs in one table. But if we want retrieve each group last records in one SQL it look like impossible.

Below Example showed how to:
SELECT rs1.*
  ON ( = AND <
Above method can get the last record results group by name.

If you want retrieve first records in each group, just change to

June 25 2013

MySQL sql duplicate insert update

Tagged Under : ,

Duplicate row found and then update the row by one sql statement. This can be done by SQL, and not need any others server-side scripting.

Now let us see how it work. First, create table and insert some example data.
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)

INSERT INTO `foo` (`id`, `name`) VALUES
(1, 'foo'),
(2, 'foo2');