MsSQL Books

Wednesday, July 22, 2009

Autopilot Income

How would you feel if I could show you how to earn your monthly income with only a couple hours prep work a month?

Read more to see the details...

Tuesday, May 19, 2009

CONCAT in MySQL

 

Here I will show you how to do string concatenation in MySQL select statement.

Assume you have a table like below

Table: employee (before concatenation)
idfirstname
lastname
1 John Doe

 

and you want your query to return like this:

Table: employee (after concatenation)
idfullname
1 John Doe

 

Its easy, the query is like this:

SELECT id, CONCAT(firstname, ' ', lastname) AS fullname FROM employee

If you want it to be return like for example: Firstname: John, Lastname: Doe

Try this: SELECT id, CONCAT('Firstname: ', firstname, ', Lastname: ', lastname) AS fullname FROM employee

See how to do this in VistaDB

Wednesday, May 17, 2006

Convert string to date in mySQL 2

In my previous blog, I've show how to convert a string in database to a datetime format. That syntax is only supported in mySQL 4.0 and above. In that version, it uses this syntax:

STR_TO_DATE(date,'%d/%m/%Y')

So, for mySQL 4.0 and below, just change the syntax to:

DATE_ADD( CONCAT(SUBSTRING_INDEX(`date`,'/',-1) , '-' , SUBSTRING_INDEX(substring_index(`date`,'/',2),'/',-1) , '-', SUBSTRING_INDEX(`date`,'/',1)), interval 0 day)

Example:

4.0 and above:
SELECT STR_TO_DATE(date,'%d/%m/%Y') AS myNewdate FROM myTable

below 4.0
SELECT DATE_ADD( CONCAT(SUBSTRING_INDEX(`date`,'/',-1) , '-' , SUBSTRING_INDEX(substring_index(`date`,'/',2),'/',-1) , '-', SUBSTRING_INDEX(`date`,'/',1)), interval 0 day) AS myNewdate FROM myTable