Convert Date to yyyyMMddhhmmss using SQL Server CONVERT

Question

I need to compare between dates by converting the SQL Server datetime value (ex: 20-Feb-14 12:52:48 PM) to yyyyMMddhhmmss

I tried the following but still need to replace the spaces and ":" and can't do multiple replace

REPLACE(RIGHT(CONVERT(VARCHAR(19), (OrderDate), 120), 19), '-', '')

Any ideas?

Here is my full code:

    Dim dsOrders As New DataSet
    Dim daOrders As SqlDataAdapter
    Dim Cnn As New SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
    Dim strSQL As String
    strSQL = "Select O.OrderID,O.Status,O.OrderDate, O.DeliveryDate, OD.Title,OD.Data from SPLBL_Orders O join SPLBL_OrderData OD on O.OrderID=OD.OrderID  where OD.LanguageID=1"

    '''' Generate Filter Results

    If txtKeyword.Text.ToString <> "" Then
        strSQL = strSQL + " OR OD.Data Like N'%" + txtKeyword.Text.ToString + "%'"
    End If

    If txtMemberID.Text.ToString <> "" Then
        strSQL = strSQL + " and O.MemberID = " + txtMemberID.Text.ToString
    End If

    If chkFeatured.Checked Then
        strSQL = strSQL + " and O.Featured=1"
    End If

    Dim lstStatus As ListItem
    Dim strStatus As String = ""
    For Each lstStatus In ddlStatus.Items
        If lstStatus.Selected Then
            If strStatus <> "" Then
                strStatus = strStatus + ","
            End If
            strStatus += lstStatus.Value.ToString()
        End If
    Next
    If strStatus <> "" Then
        strSQL = strSQL + " and O.Status IN(" + strStatus + ")"
    End If

    If txtStartDate.Text <> "" Then
        Dim strSdate As DateTime = txtStartDate.Text.ToString
        Dim strStart = strSdate.ToString("yyyyMMddhhmmss")
        If txtEndDate.Text <> "" Then
            Dim strEdate As DateTime = txtEndDate.Text.ToString
            Dim strEnd As String
            If txtEndDate.Text <> "" Then
                strEnd = strEdate.ToString("yyyyMMddhhmmss")
            Else
                strEnd = Date.Today.ToString("yyyyMMddhhmmss")
            End If
            strSQL = strSQL + " and REPLACE(REPLACE(REPLACE((CONVERT(VARCHAR(19), (OrderDate), 120)), ':', ''), '-', ''), ' ', '') Between " + strStart + " and " + strEnd + ""
        End If
    End If

    strSQL = strSQL + " order by OD.OrderID desc"

possible duplicate of [how to format getdate into YYYYMMDDHHmmSS](http://stackoverflow.com/questions/7524130/how-to-format-getdate-into-yyyymmddhhmmss) — Mikael Eriksson, Feb 09 '14 at 14:27

score 1 · Answer 1 · edited May 23 '17 at 11:56

You're causing yourself unneeded trouble because you're going about this the hard way. You should be using parameterized SQL for this (and any time you want to pass a variable value into an SQL query):

strSQL = strSQL + " and OrderDate Between @dateStart and @dateEnd"

Then pass your start date and end dates to the query as DateTime parameters with the names @dateStart and @dateEnd.

For information about using parameterized queries in VB:
How do I create a parameterized SQL query? Why Should I?

Here's essentially what you need to do. Note that I've created an SqlCommand object and added parameters to it as the query is built up. You'll need to use this SqlCommand object when you execute the actual query.

Dim dsOrders As New DataSet
Dim daOrders As SqlDataAdapter
Dim Cnn As New SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
Dim Cmd As New SqlCommand()
Cmd.Connection = Cnn

Dim strSQL As String
strSQL = "Select O.OrderID,O.Status,O.OrderDate, O.DeliveryDate, OD.Title,OD.Data from SPLBL_Orders O join SPLBL_OrderData OD on O.OrderID=OD.OrderID  where OD.LanguageID=1"

'''' Generate Filter Results

If txtKeyword.Text <> "" Then
    strSQL += " OR OD.Data Like @keyword"
    Cmd.Parameters.AddWithValue("@keyword", "%" + txtKeyword.Text + "%")
End If

If txtMemberID.Text <> "" Then
    strSQL += " and O.MemberID = @memberId"
    Cmd.Parameters.AddWithValue("@memberId", txtMemberID.Text)
End If

If chkFeatured.Checked Then
    strSQL += " and O.Featured=1"
End If

Dim lstStatus As ListItem
Dim statusCount As Integer = 1
Dim strStatus As String = ""
For Each lstStatus In ddlStatus.Items
    If lstStatus.Selected Then
        Dim paramName As String = "@status" + statusCount
        strStatus += ", " + paramName
        Cmd.Parameters.AddWithValue(paramName, lstStatus.Value.ToString)
        statusCount += 1
    End If
Next
If strStatus <> "" Then
    strSQL += " and O.Status IN(" + strStatus.Substring(2) + ")"
End If

If txtStartDate.Text <> "" Then
    Dim startDate As DateTime = DateTime.Parse(txtStartDate.Text)
    Dim endDate As DateTime
    If txtEndDate.Text <> "" Then
        endDate = DateTime.Parse(txtEndDate.Text)
    Else
        endDate = DateTime.Today
    End If
    strSQL += " and OrderDate Between @startDate and @endDate"
    Cmd.Parameters.AddWithValue("@startDate", startDate)
    Cmd.Parameters.AddWithValue("@endDate", endDate)
End If

strSQL += " order by OD.OrderID desc"

Thanks @JLRishe .. I've updated my code, I have achieved that using multiple replace but still interested in your advice. kindly apply it on my code whenever you have time. thanks — hsobhy, Feb 08 '14 at 15:55
I'm using jQuery UI date and time picker and what I thought was that I needed to do it that way to reach a common format and an easy to compare string. — hsobhy, Feb 08 '14 at 15:58
@hsobhy I suggest you read the page I linked to and find a few others to understand parameterized SQL. You should be passing all of your values into the query as parameters, not concatenating them into the query string like you are doing above. I need to run now, but I can clarify a bit later. — JLRishe, Feb 08 '14 at 16:20

Convert Date to yyyyMMddhhmmss using SQL Server CONVERT

1 Answers1