VB.net 和 SQL Server 中的日期和时间格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39284596/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Date and Time Formatting in VB.net and SQL Server
提问by Lalit
I have a table in which data is been logged in 'yy/MM/dd HH:mm:ss' format and my regional setting is '2016-04-02 14:25:15' type. I want to get details in a following query but it is not populating any results
我有一个表格，其中数据以“yy/MM/dd HH:mm:ss”格式记录，我的区域设置为“2016-04-02 14:25:15”类型。我想在以下查询中获取详细信息，但它没有填充任何结果
The query I used is
我使用的查询是
select
Date_time, alarm_id, alarm_message
from
table01
where
Date_time between '" & DateTimePicker5.Value & "' and '" & DateTimePicker6.Value & "'
I also tried using one function which I had written is
我还尝试使用我编写的一个函数是
Private Function FormatDate(ByVal dat As String) As String
Dim FTDate As Date
FTDate = FormatDateTime(Convert.ToDateTime(dat), DateFormat.ShortDate)
FormatDate = Format(FTDate, "yy/MM/dd HH:mm:ss")
End Function
And used the same again in query as
并在查询中再次使用相同的
select
Date_time, alarm_id, alarm_message
from
table01
where
Date_time between '" & formatdate(DateTimePicker5.Value) & "' and '" & formatdate(DateTimePicker6.Value) & "'
Please suggest appropriate answer make sure that I don't want to change my regional setting and on form load event. I've written the following code
请提出适当的答案，确保我不想更改我的区域设置和表单加载事件。我写了以下代码
DateTimePicker5.Format = DateTimePickerFormat.Custom
DateTimePicker5.CustomFormat = "yy/MM/dd HH:mm:ss"
DateTimePicker6.Format = DateTimePickerFormat.Custom
DateTimePicker6.CustomFormat = "yy/MM/dd HH:mm:ss"
The Table Is In Below Mentioned Format
表格采用下面提到的格式
**Datetime V1 P1**
16/08/29 19:12:24 10 STB-1
16/08/29 19:12:19 20 STB-1
16/08/29 19:12:18 30 STB-1
16/08/29 19:09:50 40 STB-1
采纳答案by Unnikrishnan R
This is some what a tricky solution. Since year kept as 'yy' in the backend,following are the assumptions.
这是一些多么棘手的解决方案。由于年份在后端保留为 'yy'，以下是假设。
if the year part of the database field is between 00 to 16 ,need to consider this as 2000 to 2016. for the remaining values script will consider year as 1917 to 1999 .
如果数据库字段的年份部分在 00 到 16 之间，则需要将此视为 2000 到 2016。对于其余值，脚本将考虑年份为 1917 到 1999 。
Can you try with the below script.
你可以试试下面的脚本。
SELECT Date_time, alarm_id, alarm_message
FROM table01
WHERE
CASE WHEN LEFT(Date_time,2) between 00 and RIGHT(year(getdate()),2) THEN CONVERT(DATETIME,'20'+Date_time)
ELSE CONVERT(DATETIME,'19'+Date_time) END between '" & DateTimePicker5.Value & "' and '" & DateTimePicker6.Value & "'
回答by Joel Coehoorn
Never ever ever EVERuse string concatenation to put values into a query like that! It's practically begging to wake up one morning and find out your site was hacked six months ago.
永远不EVER使用字符串连接把值到像这样的查询！一天早上醒来，发现您的网站在六个月前遭到黑客入侵，这实际上是在乞求。
The firstthing you need to do is fix the schema, so that your date values are actually stored as DateTime columns. There are so many reasons for this, I can't even begin to describe them all. Just do it!
您需要做的第一件事是修复架构，以便您的日期值实际上存储为 DateTime 列。这有很多原因，我什至无法开始描述所有这些。去做就对了！
Once that's done, you build the query like this:
完成后，您可以像这样构建查询：
Const SQL As String = _
"SELECT Date_time, alarm_id, alarm_message " &
" FROM table01" &
" WHERE Date_time between @StartTime AND @EndTime"
Hey, look: it's a constant. That's not strictly necessary. I usually just use a normal Dim'd String value. I wanted to prove a point here: you SQL statement is set to use specific place holders that will never at any pointhave data put in them. The values you provide for those @StartTimeand @EndTimevalues will be completely separted and quarantined from your SQL command, such that no possibility for injection ever exists.
嘿，看：这是一个常数。这不是绝对必要的。我通常只使用普通的Dim'd String 值。我想在这里证明一点：您的 SQL 语句设置为使用特定的占位符，这些占位符在任何时候都不会放入数据。您为这些@StartTime和@EndTime值提供的值将与您的 SQL 命令完全分离和隔离，因此永远不存在注入的可能性。
Once you have your SQL command string, you can use it like this (repeating the string definition so everything is in one place):
一旦你有了你的 SQL 命令字符串，你可以像这样使用它（重复字符串定义，所以一切都在一个地方）：
Const SQL As String = _
"SELECT Date_time, alarm_id, alarm_message " &
" FROM table01" &
" WHERE Date_time between @StartTime AND @EndTime"
Using cn As New SqlConnection("connection string here"), _
cmd As New SqlCommand(SQL, cn)
cmd.Parameters.Add("@StartTime", SqlDbType.DateTime).Value = DateTimePicker5.Value
cmd.Parameters.Add("@EndTime", SqlDbType.DateTime).Value = DateTimePicker6.Value
cn.Open()
Using rdr As SqlDataReader = cmd.ExecuteReader()
While rdr.Read()
End While
End Using
End Using
Or if you're filling a DataTable:
或者，如果您正在填充数据表：
Dim result As New DataTable
Const SQL As String = _
"SELECT Date_time, alarm_id, alarm_message " &
" FROM table01" &
" WHERE Date_time between @StartTime AND @EndTime"
Using cn As New SqlConnection("connection string here"), _
cmd As New SqlCommand(SQL, cn), _
ad As New SqlDataAdapter(cmd)
cmd.Parameters.Add("@StartTime", SqlDbType.DateTime).Value = DateTimePicker5.Value
cmd.Parameters.Add("@EndTime", SqlDbType.DateTime).Value = DateTimePicker6.Value
ad.Fill(result)
End Using
Note that using this method, you never have to worry about your DateTime format. ADO.Net figures it out for you. It knows about .Net DateTime objects, and it knows about Sql Server DateTime columns, and it handles conversions between the two types naturally.
请注意，使用此方法，您永远不必担心您的 DateTime 格式。ADO.Net 为您解决了这个问题。它知道 .Net DateTime 对象，也知道 Sql Server DateTime 列，它自然地处理两种类型之间的转换。
回答by Munavvar
You can use Convert function.
您可以使用转换功能。
select
Date_time, alarm_id, alarm_message
from
table01
where
Convert(Datetime,Date_time) between Convert(Datetime,'" & DateTimePicker5.Value & "') and Convert(Datetime,'" & DateTimePicker6.Value & "')
回答by shadow
Since:
自从：
Console.WriteLine(#1/31/2016 10:23:22 AM#.ToString("yy/MM/dd HH:mm:ss"))
returns 16/01/31 10:23:22
返回 16/01/31 10:23:22
I guess that:
我猜可能是：
select
Date_time, alarm_id, alarm_message
from
table01
where
(Date_time >= '" & DateTimePicker5.Value.ToString("yy/MM/dd HH:mm:ss") & "' )
and (Date_time < '" & DateTimePicker6.Value.ToString("yy/MM/dd HH:mm:ss") & "')
will do the trick.
会做的伎俩。
Noticethat I've changed the between with simple compare conditions
请注意，我已经使用简单的比较条件更改了
Run this in SQL Server and check the results:
在 SQL Server 中运行它并检查结果：
create table #dateTest (
testDate datetime
)
insert into #dateTest values ('2016-03-01')
select * from #dateTest
declare @fromDate datetime
set @fromDate = '2016-01-01'
declare @toDate datetime
set @toDate = '2016-03-01'
select
testDate
from
#dateTest
where
( testDate between @fromDate and @toDate )
-- 2016-03-01 00:00:00.000
select
testDate
from
#dateTest
where
( testDate <= @fromDate )
and ( testDate < @toDate )
-- No rows selected
drop table #dateTest
