如何插入“0000-00-00”格式化日期到mysql数据库？ [英] how to insert "0000-00-00" formatted date into mysql database?

问题描述

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")

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()

我从tb_kna_analysis_tv数据库中选择日期值a并在tb_kna_analysis_tvdb中插入日期。但我面临的问题是，如果我想选择（检索）的日期值是0000-00-00，那么它会给我一个错误。但是我的代码工作正常，如果日期值不是0000-00-00（来自这样的空值的aprt）

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)

推荐答案

试试MySQL DATE_FORMAT（）函数 [ ^ ]。

它给你一个错误，因为0000-00-00不是一个有效的日期，部分是因为本月没有第0个，甚至一年中没有第0个月，部分是因为SQL DATE最小值是1753年1月1日和2017-01-01为MySql。



您需要在转移数据之前检查并验证数据，这就是全部。没有什么可以做的来说服数据领域采取EA！



请学习使用参数化查询！在不知道你的数据库中究竟是什么的情况下，你可以使用这样的代码对SQL注入做好准备！

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!

你没有！数据库以二进制格式存储日期而不是字符串 - 格式化属于应用程序的UI部分...

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

这篇关于如何插入“0000-00-00”格式化日期到mysql数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！