Sql查询从字符串中检索包含所有格式的日期的记录 [英] Sql query to retrieve records that contains date of all formats from a character string

问题描述

我在谷歌搜索过。但无处可寻，我找不到答案。无处不在提到要检索特定日期，转换日期格式或查找日期之间的差异



我尝试过：



我不知道从字符中检索日期的特定关键字

解决方案

简单：不要。



永远不要将日期存储为字符串：输入后始终将它们转换为Date或DateTime值 - 因为只有这样才能访问任何日期或日期时间值提供数据信息背景的文化信息。没有它，你就无法转换它。

不相信我？这是几号？

 01/02/03 

如果您的用户是美国用户，可能是2003年1月2日。

如果他是欧洲人，可能是2003年2月1日。

如果他是日本人，那可能是2001年2月3日。



你只能通过观察它无法分辨出来。一旦字符串击中您的数据库，您就不再拥有任何文化信息，并且日期 - 坦率地说 - 总垃圾。这就是为什么你在互联网上找不到任何关于将字符串日期转换为任何格式的实际日期的原因。


因此重新设计你的数据库，重新编写你的演示软件，然后通过正确，通过参数化查询确认正确的日期为DateTime数据。



为什么最后一位？因为大多数情况下，如果将日期存储为字符串，那是因为您连接字符串以形成SQL命令。这意味着你有另一个更糟糕的问题：你的用户可能随时损坏或破坏你的数据库...

永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。



连接字符串时会导致问题，因为SQL会收到如下命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  Baker' s Wood '   

就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  x';  DROP  表 MyTable;   -   ' 

哪个SQL看作三个单独的命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  x'; 

完全有效的SELECT

  DROP   TABLE  MyTable; 

完全有效的删除表格通讯和

   -   ' 

其他一切都是评论。

所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。



所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你定期做备份，不是吗？

I searched in google. But nowhere i couldn't find answer for this. Everywhere it is mentioned to retrieve a particular date, to convert the date format or to find difference between the dates

What I have tried:

I don't know a particular keyword to retrieve only date from a character

解决方案

Simple: Don't.

Never store dates as strings: always convert them to Date or DateTime values as soon as they are entered - because only then do you have access to any cultural information which provides context for the data info. Without that, you can't convert it.
Don't believe me? What date is this?

01/02/03

If your user is American, it's probably 2nd January, 2003.
If he is European, it's probably 1st February, 2003.
If he is Japanese, it's probably 3rd February, 2001.

And you can't tell which just by looking at it. Once the string hits your DB, you no longer have any cultural information, and the date is - frankly - total garbage. That's why you don't find anything on the internet about converting string dates to actual dates for any format.

So redesign your DB, rework your presentation software, and pass proper, confirmed correct dates as DateTime data via a parameterised query.

Why the last bit? Because most of the time, if you store dates as strings, it's because you concatenate strings to form SQL commands. And that means you have another, much worse problem: your users can damage or destroy your database any time they want...
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

DROP TABLE MyTable;

A perfectly valid "delete the table" command

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

这篇关于Sql查询从字符串中检索包含所有格式的日期的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！