Post reply

ASP and SQL Help

  • June 7, 2011 at 7:26 am

    #239572

    I have export a database from ms access to SQL server. now i need to change few coding syntax in from access to SQL. here 'Format' is not a recognized function name. i changed the format into formatdatetime but it is not working. what should i do now? please help urgent

    SQL = "Select r.Req_Code,Format(rc.ChargeDate,'dd/mmm/yyyy') as ChargeDate,Format(r.req_date,'dd/mmm/yyyy') as req_date, rc.TotalCharges,r.RequestorName,r.Department,r.Budget_Code,Format(r.To_Date,'dd/mmm/yyyy') as To_Date,r.Visit_Type from Requisition as r,Req_Charges as rc " & _

    "where rc.chargeDate >= #" & StartDTTM & "# and rc.chargeDate <= #" & EndDTTM & "# " & _

    "and rc.Req_ID = r.Req_ID order by r.Req_Code,rc.ChargeDate"

  • June 7, 2011 at 7:37 am

    #1335007

    SQL uses the CONVERT function to format dates...there's an optional parameter, like 101, which means dd/mm/yyyy; you can read the details inbooks online under "Cast and Convert"

    i think this is how i would rewrite your code:

    sql = "DECLARE @StartDTTM datetime, " & vbCrLf

    sql=sql & " @EndDTTM datetime " & vbCrLf

    sql=sql & "SET @StartDTTM = '" & StartDTTM & "' " & vbCrLf

    sql=sql & "SET @EndDTTM = '" & EndDTTM & "' " & vbCrLf

    sql=sql & " " & vbCrLf

    sql=sql & "SELECT " & vbCrLf

    sql=sql & " r.Req_Code, " & vbCrLf

    sql=sql & " CONVERT(VARCHAR(35),rc.ChargeDate,101) AS ChargeDate, " & vbCrLf

    sql=sql & " CONVERT(VARCHAR(35),r.req_date,101) AS req_date, " & vbCrLf

    sql=sql & " rc.TotalCharges, " & vbCrLf

    sql=sql & " r.RequestorName, " & vbCrLf

    sql=sql & " r.Department, " & vbCrLf

    sql=sql & " r.Budget_Code, " & vbCrLf

    sql=sql & "CONVERT(VARCHAR(35),r.To_Date,101) AS To_Date, " & vbCrLf

    sql=sql & "r.Visit_Type " & vbCrLf

    sql=sql & "FROM Requisition AS r " & vbCrLf

    sql=sql & "INNER JOIN Req_Charges AS rc " & vbCrLf

    sql=sql & "ON rc.Req_ID = r.Req_ID " & vbCrLf

    sql=sql & "WHERE rc.chargeDate >= @StartDTTM " & vbCrLf

    sql=sql & " AND rc.chargeDate <= @EndDTTM " & vbCrLf

    sql=sql & "ORDER BY " & vbCrLf

    sql=sql & " r.Req_Code, " & vbCrLf

    sql=sql & " rc.ChargeDate " & vbCrLf

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply