Date and Time Formatting in VB.net and SQL Server

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

  • Get SQL Server Instances in Windows 10
  • Will TSQL return faster results than stored procedure in SQL Server
  • SQL Server trigger doesn't fire when a record is inserted from ASP.NET web page
  • How to design Query for creating dynamic columns from rows
  • LIKE using subquery returning multiple rows
  • Bit-flipping operations in T-SQL
  • 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 
    

  • Does SYSDATETIME() cost more than GETDATE()?
  • How to get first and last day of previous month (with timestamp) in SQL Server
  • Group by datetime ignoring time portion
  • When storing a datetime in sql server (datetime type), what format does it store it in?
  • What is the difference between JAVA date format “dd/MM/yyyy” and “dd/mm/yyyy”?
  • How can I convert a JSON date with timezone to a SQL Server datetime?
  • 4 Solutions collect form web for “Date and Time Formatting in VB.net and SQL Server”

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

    Never ever ever EVER use 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.

    The first thing 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!

    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 point have data put in them. The values you provide for those @StartTime and @EndTime values will be completely separted and quarantined from your SQL command, such that no possibility for injection ever exists.

    Once you have your SQL command string, you can use it like this (repeating the string definition so everything is in one place):

    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.

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

    Since:

    Console.WriteLine(#1/31/2016 10:23:22 AM#.ToString("yy/MM/dd HH:mm:ss"))
    

    returns 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.

    Notice that I’ve changed the between with simple compare conditions

    Run this in SQL Server and check the results:

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.