VB.net和SQL Server中的日期和时间格式 [英] Date and Time Formatting in VB.net and SQL Server

问题描述

我有一个表格，其中数据以 yy / MM / dd HH：mm：ss格式记录，而我的区域设置是 2016-04-02 14:25:15类型。我想在以下查询中获取详细信息，但它不会填充任何结果

我使用的查询是

 从
 table01 
中选择
 Date_time，alarm_id，alarm_message 
其中
 Date_time在'& DateTimePicker5.Value& '和'& DateTimePicker6.Value&'
  

我也尝试使用一个函数我写的是

 私有函数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）
结束函数
  

在查询中再次使用

 从
 table01 
中选择
 Date_time，alarm_id，alarm_message 
其中
 Date_time在'& formatdate（DateTimePicker5.Value）&之间'和'&格式化ate（DateTimePicker6.Value）& ''
  

请提出适当的答案，以确保我不想更改自己的区域设置，并在表单加载事件。我编写了以下代码

  DateTimePicker5.Format = DateTimePickerFormat.Custom 
 DateTimePicker5.CustomFormat =  yy / MM / dd HH：mm：ss 
 DateTimePicker6.Format = DateTimePickerFormat.Custom 
 DateTimePicker6.CustomFormat = yy / MM / dd HH：mm：ss 
  

表格采用以下格式

  ** 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 
  

解决方案

这是一个棘手的解决方案。
由于年份在后端保持为 yy，因此以下是假设。 / p>

如果数据库字段的年份部分在00到16之间，则需要考虑这是从2000年到2016年。对于剩余值脚本，会将年份视为1917年到1999年。

可以尝试以下脚本吗？

 选择date_time，alarm_id，alarm_message 
 from table01 
 WHERE 
在左移（Date_time，2）在00到RIGHT（ year（getdate（）），2）THEN CONVERT（DATETIME，'20'+ Date_time）
 ELSE CONVERT（DATETIME，'19'+ Date_time）END在'& DateTimePicker5.Value& ’和‘& DateTimePicker6.Value& ’
  

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

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 

解决方案

This is some what a tricky solution. Since year kept as 'yy' in the backend,following are the assumptions.

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 .

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 & "'

这篇关于VB.net和SQL Server中的日期和时间格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！