locked
Get today's day RRS feed

  • Question

  • User546194788 posted

    Data type in a column [PostedDate] is datetime, display like "2019-02-13 09:15:53.603"

    To get Today's date I have to use the SQL statement below.

    Dim Sql As String = "select name from tableUser where posteddate > '" & DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") & "' and posteddate < '" & DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") & "'"

    How to get today's format so that I will modify like "where posteddate = something here"?

    Wednesday, February 13, 2019 8:21 PM

All replies

  • User475983607 posted

    Data type in a column [PostedDate] is datetime, display like "2019-02-13 09:15:53.603"

    To get Today's date I have to use the SQL statement below.

    Dim Sql As String = "select name from tableUser where posteddate > '" & DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") & "' and posteddate < '" & DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") & "'"

    Use a parameter query over string built SQL.  Dynamic SQL like posted above is subject to SQL injection attacks.  Plus parameters simple handle the formatting for you automatically.

    https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=netframework-4.7.2

    How to get today's format so that I will modify like "where posteddate = something here"?

    If you really want to build dynamic SQL and not afraid of SQL injection, the see the .NET DateTime formats.  You must be very very careful when manually formatting dates because cultures have different ways for representing a Date.  It is much safer and far easier to build a parameter query.  But here are the standard Datetime format and you can build it anyway you like.

    https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

    Wednesday, February 13, 2019 8:44 PM
  • User-1716253493 posted

    where cast([PostedDate] as Date) = 

    Thursday, February 14, 2019 1:24 AM
  • User-158764254 posted

    where cast([PostedDate] as Date) = 

    doing a query that way will cause a table scan and cannot take advantage of indexes. So as the number of rows in the table increases, the performance decreases.

    general recommendation is to be cautious about and try to avoid functions on the left side of the operator.

    Thursday, February 14, 2019 1:32 AM