CODEWITHSUNDEEP
mysqlsqlstringdate
Zaffar Saffee
Zaffar Saffee

Reputation: 6305

What is Date? a string? or Double?

Okay, That might be asked somewhere, but I am unable to find it over google or SO,

My query was

INSERT INTO Tracking (OrderNum, PickupDate, ...) VALUES (95370,10/01/2013, .....)

plate form was a php script updating MS-Access db..and target column pickupDate was of Date/Time data type in access db..

when I ran this query, the information inserted in DB wasn't 10/01/2013, rather its was long digit like 34444444299384 (something like that, didnt noted that exactly)..

but when I use the following query

INSERT INTO Tracking (OrderNum, PickupDate, ...) VALUES (95370,'10/01/2013', .....)

So for what I know is, anything between the qoutes is a STRING

Can somebody please explain it to me??

thanks for your guideline..

Upvotes: 2

Views: 11569

Answers (3)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

When you use:

10/01/2013

it is an expression: 10 divided by 1 divided by 2013, giving as a result: 0,0049677... which is then converted to a date, as dates are really numbers.

When you use:

'10/01/2013'

It's a string, which is converted into a date.

Internally, a date is stored as a number, not a string.

Upvotes: 4

Bailey Parker
Bailey Parker

Reputation: 15905

My guess is that 10/01/2013 was interpreted as a division operation, so the resulting number was a small floating point number:

0.00496770988

From there, MySQL probably assumed you gave it a UNIX timestamp and it mangled the number to try to convert it into a storeable date.

What you want to do instead is give MySQL the format it prefers YYYY-MM-DD and quote it:

'2013-10-01'

More on MySQL Dates

Upvotes: 4

elixenide
elixenide

Reputation: 44841

If you are using DATE as the column type, your input is wrong. MySQL expects '2013-10-01', not '10/01/2013'. See the manual.

Upvotes: 0

Related Questions