Database Blog

Autopilot Income

2009-07-22T10:38:00.002+08:00

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

Free Report Reveals How To Earn Autopilot Income For Life... Starting From Scratch!

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

I do it, and now I'm going to help you do the same...

Right now, I'm giving away quite possibly the most valuable guidebook you'll ever own.

Here's just a sample of what you'll discover...

>>	A step-by-step plan that starts from scratch & takes you to unimagined heights
>>	How to setup passive income for life
>>	A method easy enough for even the rawest rookie to understand & start implementing today.
>>	The “secret tweak” gurus have been using to generate millions of dollars right before your eyes…
>>	How to print money on demand like magic whenever you want

Grab Your Free Guide To A Lifetime Of Passive Income

CONCAT in MySQL

2009-05-19T16:01:00.002+08:00

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)
id	firstname	lastname
1	John	Doe

and you want your query to return like this:

Table: employee (after concatenation)
id	fullname
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

Convert string to date in mySQL 2

2006-05-17T17:37:00.001+08:00

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

Convert string to date in mySQL

2006-04-18T16:13:00.001+08:00

if you have a date column in a table that has a string datatype, it is impossible to query or find the record between 2 durations of date. for example, you have a table "myTable"

myTable

---------------------------
Column | Type
---------------------------
date | varchar

it is impossible to query like this
SELECT * FROM myTable WHERE date>='@dtFrom' AND date<='@dtTo'the correct way to get the record between the 2 dates is like thisSELECT * FROM myTable WHERE STR_TO_DATE(date,'%d/%m/%Y') <= '@dtFrom' AND STR_TO_DATE(date,'%d/%m/%Y') <= '@dtTo'STR_TO_DATE(date,'%d/%m/%Y') the type '%d/%m/%Y' is depend on how you store the date into the table. In my case, I store it as 18/04/2006more help can be found here - dev.mysql.com

Introduction

2006-03-27T10:54:00.000+08:00

Just an introduction. This blog will be discussing on programming with database or database query. Stay tuned for the next update.