Bobski Bobski - 1 year ago 54
SQL Question

SQL SERVER date/time inconsistency

I have a field in SQL Server with datatype of Datetime. I'm selecting the date simply as

select impDate from tbl1

What's happening is - the am/pm part is not consistent...Please see image

enter image description here

As can be seen - it seems to work fine for everything under 12:00 and over until it gets to 12:59 - at this point each hour is recognized as AM - when 2:46 and 3:17 should be PM.

This is what it looks like in sql server...

2016-10-18 10:25:16.000


DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss")

Answer Source

I am going to take a guess based on the information in the question that you are using the string value of the current date and time using hh to retrieve the hours which is a 12 hour format. If you use string concatination to build up your sql (really bad, you should use parameters) this would explain the problem.

However, the fix is not to use military time to insert the date but to use a parameterized query and pass the DateTime instance directly to the value of the parameter.

Here is a quick example of how to use a parameter to avoid this type of issue in the future.

Public Function SomeFunction(ByVal dateToInsert As DateTime)
    Dim sql As String = "INSERT INTO tbl1 (impDate) VALUES(@impDate)"

    Using cn As New SqlConnection("Your connection string here"), _
        cmd As New SqlCommand(sql, cn)

        cmd.Parameters.Add("@impDate", SqlDbType.DateTime).Value = dateToInsert
    End Using
End Function

See also Best Practices - Executing Sql Statements