how to insert "0000-00-00" formatted date into mysql database?

Question

0.00/5 (No votes)

See more:

VB

Dim programDate_date24 As Date = reader.GetString(24)
               Dim bd24 As String = programDate_date24.Year.ToString.PadLeft(4, "0") + "-" + programDate_date24.Month.ToString.PadLeft(2, "0") + "-" + programDate_date24.Day.ToString.PadLeft(2, "0")

SQL

Dim clipdetails_ctn_insert As New MySqlCommand("insert into tb_kna_analysis_tv (id ,channel_master_id,  program_master_id,  programTelecast,programDate,programStartTime,programEndTime,programLength,programSlot,isGEC,headline,reach,trp,sharedOrExclusive,clipType,typeComment,clipTag,tagCommentOther,tagCommentSpecialFeature,qcAutoUserId,qcAutoCurrentTime,qcAutoCurrentDate,qcReleaserId,qcReleaserCurrentTime,qcReleaserCurrentDate,user_master_id,currentTime,currentDate,c2n,c2nTime,c2nDate,clip_core_id,clip_start_time,clip_end_time,hourly_clip_name,cropped_clip_name,advertise_flag,breaking_news_flag,sector_id,keyword_list,is_headline,is_logo)  values('" + txtbx_CopyToNew.Text + "','" + bct1.ToString() + "','" + bct2.ToString() + "','" + bct3.ToString() + "','" + bd + "','" + bct5.ToString() + "','" + bct6.ToString() + "','" + bct7.ToString() + "','" + bct8.ToString() + "','" + bct9.ToString() + "','" + bct10.ToString() + "','" + bct11.ToString() + "','" + bct12.ToString() + "','" + bct13.ToString() + "','" + bct14.ToString() + "','" + bct15.ToString() + "','" + bct16.ToString() + "','" + bct17.ToString() + "','" + bct18.ToString() + "','" + bct19.ToString() + "','" + bct20.ToString() + "','" + bd21 + "','" + bct22.ToString() + "','" + bct23.ToString() + "','" + bd24 + "','" + bct25.ToString() + "','" + Format(DateTime.Now, "HH:mm:ss").ToString + "','" + Format(DateTime.Now, "yyyy-MM-dd").ToString + "','" + bct28.ToString() + "','" + bct29.ToString() + "','" + bd30 + "','" + bct31.ToString() + "','" + bct32.ToString() + "','" + bct33.ToString() + "','" + bct34.ToString() + "','" + bct35.ToString() + "','" + bct36.ToString() + "','" + bct37.ToString() + "','" + bct38.ToString() + "','" + bct39.ToString() + "','" + bct40.ToString() + "','" + bct41.ToString() + "')", conn)
                reader1 = clipdetails_ctn_insert.ExecuteReader()
                reader1.Close()

I am selecting date values a from "tb_kna_analysis_tv" database and inserting a date into "tb_kna_analysis_tv" db . but the problem I am facing is if the date value which i want to select(retrieve) is 0000-00-00 then it gives me an error . but my code works fine if date value is not 0000-00-00(aprt from such a null value)

Posted 25-Feb-14 23:04pm

mayur patil

Updated 25-Feb-14 23:18pm

v3

Add a Solution

3 solutions

Solution 2

Try out the examples given at MySQL DATE_FORMAT() Function[^].

Posted 25-Feb-14 23:07pm

Abhinav S

Solution 1

You do not! Databases store date in binary format and not as string - formatting is belongs to the UI part of the application...

Posted 25-Feb-14 23:07pm

Kornfeld Eliyahu Peter

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-02-25T23:11:00

It gives you an error because "0000-00-00" is not a valid date, partly because there is no "zeroth" of the month, or even zeroth month of the year, and partly because the SQL DATE minimum value is January 1st 1753 and 0001-01-01 for MySql.

You need to check and validate your data before you transfer it, is all. There is nothing you can do to persuade a data field to take 0000-00-00!

And please, learn to use parametrized queries! Without knowing what the heck is in your database, you leave yourself wide open to SQL Injection with code like that!