using sql function in ADO

[baha17]

Dear All,

I tried to run below code but it continuesly give me error that, DAYNAME, MONTHNAME and TIME is not recognized built in function.
Is there anyway you can helpw me get those from sql server by using excel. Time needs to be in "HH:MM" format.
I do not want to use for next then change the format of the column because it consumes a lot of time.

Thanks in advance

Baha

Here is my code

Sub Run_Ver2()
Dim DBFullName, TableName As String
Dim TargetRange As Range
Dim Conn As ADODB.Connection, intColIndex As Integer
Dim LastRow As Long
Dim datepick As Variant
Dim datepick1 As Variant
Dim datepick2 As Variant
Dim cel As Range
datepick1 = Format(Range("trddate"), "YYYY-MM-DD") & " " & Range("timeval").Text & ":00"
datepick2 = Format(Range("trddate2"), "YYYY-MM-DD") & " " & Range("timeval").Text & ":00"
    Sheets("TableData").Range("A2:J1048576").ClearContents
    Sheets("TableData").Select
Set TargetRange = Range("A2")
    Set Conn = New ADODB.Connection
     Conn.Open "driver={SQL Server};" & _
    "server=abcsql;database=MarketSales;"
        Set RecSet = New Recordset
    RecSet.Open "SELECT   headcount.counter_id,   " & _
    "headcount.minsale, setup_MarketSales.shop_name, " & _
    "replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(headcount.counter_id,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''), " & _
    "headcount.headcount_datetime, DAYNAME(headcount.headcount_datetime), MONTHNAME(headcount.headcount_datetime), TIME(headcount.headcount_datetime)  " & _
    "FROM MarketSales.dbo.headcount headcount, MarketSales.dbo.setup_MarketSales setup_MarketSales " & _
    "WHERE headcount.counter_id = setup_MarketSales.counter_id AND ((headcount.headcount_datetime>={ts '" & datepick1 & "'})) " & _
    "AND ((headcount.headcount_datetime<={ts '" & datepick2 & "'}))", Conn, , , adCmdText
        TargetRange.CopyFromRecordset RecSet
    RecSet.Close
    Set RecSet = Nothing
    Conn.Close
    Set Conn = Nothing
LastRow = Sheets("TableData").Range("A" & Sheets("TableData").Rows.Count).End(xlUp).Row
Columns("E:E").NumberFormat = "dd/mmm/yy"
    Range("A1").Select
End Sub

 

[baha17]

i think i found solution. i put {fn MONTHNAME(...)} in the code

Sub Run_Ver2()
Dim DBFullName, TableName As String
Dim TargetRange As Range
Dim Conn As ADODB.Connection, intColIndex As Integer
Dim LastRow As Long
Dim datepick As Variant
Dim datepick1 As Variant
Dim datepick2 As Variant
Dim cel As Range
datepick1 = Format(Range("trddate"), "YYYY-MM-DD") & " " & Range("timeval").Text & ":00"
datepick2 = Format(Range("trddate2"), "YYYY-MM-DD") & " " & Range("timeval").Text & ":00"
    Sheets("TableData").Range("A2:J1048576").ClearContents
    Sheets("TableData").Select
Set TargetRange = Range("A2")
    Set Conn = New ADODB.Connection
     Conn.Open "driver={SQL Server};" & _
    "server=abcsql;database=MarketSales;"
        Set RecSet = New Recordset
    RecSet.Open "SELECT   headcount.counter_id,   " & _
    "headcount.minsale, setup_MarketSales.shop_name, " & _
    "replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(headcount.counter_id,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''), " & _
    "headcount.headcount_datetime, {fn DAYNAME(headcount.headcount_datetime)}, {fn MONTHNAME(headcount.headcount_datetime)}, {fn HOUR(headcount.headcount_datetime)}  " & _
    "FROM MarketSales.dbo.headcount headcount, MarketSales.dbo.setup_MarketSales setup_MarketSales " & _
    "WHERE headcount.counter_id = setup_MarketSales.counter_id AND ((headcount.headcount_datetime>={ts '" & datepick1 & "'})) " & _
    "AND ((headcount.headcount_datetime<={ts '" & datepick2 & "'}))", Conn, , , adCmdText
        TargetRange.CopyFromRecordset RecSet
    RecSet.Close
    Set RecSet = Nothing
    Conn.Close
    Set Conn = Nothing
LastRow = Sheets("TableData").Range("A" & Sheets("TableData").Rows.Count).End(xlUp).Row
Columns("E:E").NumberFormat = "dd/mmm/yy"
    Range("A1").Select
End Sub