将日期时间从VB6插入/更新到SQL服务器 [英] Insert/update datetime from VB6 to SQL server

问题描述

在MS ACCESS中

itmarr（0,0）=bill_date

itmarr（1,0）= TxtDate.Text



在SQL SERVER中

itmarr（0,0）=bill_date

itmarr（1,0）=格式（TxtDate.Text， dd MMM yyyy）



SQL SERVER中的错误显示]

将varchar数据类型转换为日期时间数据类型导致超出范围价值



我尝试过：

In MS ACCESS
itmarr(0, 0) = "bill_date"
itmarr(1, 0) = TxtDate.Text

IN SQL SERVER
itmarr(0, 0) = "bill_date"
itmarr(1, 0) = Format(TxtDate.Text, "dd MMM yyyy")

ERROR SHOW IN SQL SERVER]
the conversion of a varchar data type to a datetime data type resulted in an out-of-range value

What I have tried:

Insert/update datetime from VB6 to Sql Server

推荐答案

在传递时总是尝试使用明确的日期周围的信息 - 特别是来往于数据库。



这究竟意味着什么？这意味着使用一种日期格式，无论您在哪个国家/地区或时区都使用哪种语言都不会被误解。



示例：01/06 / 2016将于6月1日在英国，但1月6日在美国。



你们非常接近因为你使用了dd MMM yyyy作为格式。这在大多数语言中都很好......2017年2月27日适用于英语，荷兰语，德语等，但在芬兰语中没有意义（2月的芬兰语是helmikuu）。现在，将SQL Server服务器托管在运行应用程序的不同国家/地区并不罕见。让来自多个国家/地区的用户访问同一个SQL数据库也不常见。



有两种格式的日期可以保证适用于所有版本的SQL Server。它们也适用于大多数其他数据库。

Always try to use "unambiguous dates" when passing information around - especially to and from databases.

What does that actually mean? It means use a date format that cannot be misinterpreted no matter which in country or time zone you're in nor which language is being used.

Example: 01/06/2016 would be 1st June in the UK but 6th January in the USA.

You are pretty close because you have used dd MMM yyyy as the format. Which is fine in most languages ... "27 Feb 2017" works in English, Dutch, German, etc etc but in Finnish would be meaningless (Finnish for February is helmikuu). It's not unusual these days to have SQL Server servers hosted in different countries to the one where the application is running. Nor is it unusual to have users from multiple countries accessing the same SQL database.

There are two formats for dates that are guaranteed to work for all versions of SQL Server. Usefully they work for most other databases as well.

yy-MM-ddTHH24:mi:ss

和

yyyyMMdd HH24:mi:ss

同样值得阅读ISO标准的日期 - ISO 8601 - Wikipedia [ ^ ]



如果这不能解决您的具体问题，那么在

It's also worth having a read up on the ISO standard for dates - ISO 8601 - Wikipedia[^]

If this doesn't solve your specific problem, then put a breakpoint (F9) on the line

itmarr(1, 0) = Format(TxtDate.Text, "dd MMM yyyy")

并查看TxtDate的内容 - 您可能会发现它根本无法格式化为日期。您应该添加一些验证或使用DatePicker控件。

and have a look at the contents of TxtDate - you'll probably find that it can't be formatted as a date at all. You should really add some validation OR use a DatePicker control.

这篇关于将日期时间从VB6插入/更新到SQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！