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

  • What is LINQ equivalent of SQL’s “IN” keyword
  • Is there a quick way to check if ANY column is NULL?
  • insert into one table from another table using join update or insert SQL server
  • SQL Join with table including from/to values
  • Post JSON data to Sql Server report services 2016 with web api
  • Column name or number of supplied values does not match table definition
  • 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 
    

  • ISO8601 Convert Week Date to Calendar Date
  • select datetime closest to a specified value for every day in a year
  • How Can I use “Date” Datatype in sql server?
  • What is the difference between JAVA date format “dd/MM/yyyy” and “dd/mm/yyyy”?
  • Set time portion of a datetime variable
  • Convert nvarchar(50) to datetime (Second's part not coming after update the table)
  • 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.