Aiuto conversione SQL da Access a Sql Server

[Surrender79]

ciao, ho del codice sql che eseguo tramite asp su db access e dovrei convertirlo in codice funzionante per sql server.

Questo è l'sql per ACCESS:

SELECT Mid(CStr(DateTimeCall),1,10) AS Data, Count(Mid(CStr(DateTimeCall),1,10)) As Chiamate 
FROM Chiamate 
WHERE (
(DateTimeCall >= #7/4/2007 00.00.00# AND DateTimeCall <= #7/21/2007 23.59.59# 
AND weekday(DateTimeCall) <> 1 AND weekday(DateTimeCall) <> 7 
AND (TimeValue(DateTimeCall) >= #08.00.00# And TimeValue(DateTimeCall) <= #12.30.59# )
) 
GROUP BY Mid(CStr(DateTimeCall),1,10)
ORDER BY CDate(Mid(CStr(DateTimeCall),1,10)) ASC

Questo è l'sql che ho scritto al momento per SQL SERVER:

SELECT SUBSTRING(CAST(DateTimeCall AS VARCHAR(10)), 1, 10) AS Data, 
Count(SUBSTRING(CAST(DateTimeCall AS VARCHAR(10)), 1, 10)) As Chiamate 
FROM Chiamate 
WHERE (
(CONVERT(DATETIME, DateTimeCall, 102)>= '2007-07-04 00:00:00' AND CONVERT(DATETIME, DateTimeCall, 102) <= '2007-07-21 23:59:59'
AND DATENAME(weekday , DateTimeCall) <> 'Domenica' DATENAME(weekday , DateTimeCall) <> 'Sabato'
AND (TimeValue(DateTimeCall) >= #08.00.00# And TimeValue(DateTimeCall) <= #12.30.59# )
)
GROUP BY SUBSTRING(CAST(DateTimeCall AS VARCHAR(10)), 1, 10)
ORDER BY CDate(Mid(CStr(DateTimeCall),1,10)) ASC

Però mi viene errata la conversione della data, anzichè scrivere, ad esempio, 14/07/2007 mi scrive lug 14 2007

Altra cosa: devo anche riuscire a usare una funzione corrispondente a TimeValue, cioè che mi estragga hh.mm.ss dalla data...

Se qualcuno ha suggerimenti per trasformare questo sql mi farebbe un grandissimo favore, nel frattempo continuo a sperimentare

[Surrender79]

Ok, ho risolto così e i dati mi tornano quindi credo sia corretto :master:

select 
CONVERT(CHAR(10), DateTimeCall, 120) as Data,
Count(CONVERT(CHAR(10), DateTimeCall, 120)) as Chiamate
from Chiamate
where (
(
CONVERT(CHAR(10), DateTimeCall, 120)>= '2007-07-04' AND CONVERT(CHAR(10), DateTimeCall, 120) <= '2007-07-21'
AND DATENAME(weekday , DateTimeCall) <> 'Domenica' AND DATENAME(weekday , DateTimeCall) <> 'Sabato'
AND (
CONVERT(CHAR(8), DateTimeCall, 108) >= '08:00:00' And CONVERT(CHAR(8), DateTimeCall, 108) <= '12:30:59'
OR CONVERT(CHAR(8), DateTimeCall, 108) >= '13:30:00' And CONVERT(CHAR(8), DateTimeCall, 108) <= '18:30:59'
) OR (
CONVERT(CHAR(10), DateTimeCall, 120)>= '2007-07-04' AND CONVERT(CHAR(10), DateTimeCall, 120) <= '2007-07-21'
AND DATENAME(weekday , DateTimeCall) = 'Sabato' 
AND CONVERT(CHAR(8), DateTimeCall, 108) >= '08:00:00' And CONVERT(CHAR(8), DateTimeCall, 108) <= '12:30:59'
)
)
)

group by 
CONVERT(CHAR(10), DateTimeCall, 120)
ORDER BY 
CONVERT(CHAR(10), DateTimeCall, 120) ASC