Conversione stringa in data

USE tempdb;
GO

CREATE TABLE dbo.ElencoFatture(
numfattura int NOT NULL PRIMARY KEY,
cliente varchar(10) NOT NULL,
data char(10) NOT NULL
);

INSERT dbo.ElencoFatture VALUES
      (1, 10, '01/01/2000')
    , (2, 10, '02/01/2000')
    , (3, 20, '14/12/2003')
    , (4, 30, '15/12/2003')
    , (5, 30, '06/05/2008');
GO

ALTER TABLE dbo.ElencoFatture
ADD data2 AS CAST(
      SUBSTRING(data, 7, 4) -- Anno
    + SUBSTRING(data, 4, 2) -- Mese
    + SUBSTRING(data, 1, 2) -- Giorno
AS smalldatetime);
GO

SELECT *
FROM dbo.ElencoFatture;

/* Output:

numfattura  cliente    data       data2
----------- ---------- ---------- -----------------------
1           10         01/01/2000 2000-01-01 00:00:00
2           10         02/01/2000 2000-01-02 00:00:00
3           20         14/12/2003 2003-12-14 00:00:00
4           30         15/12/2003 2003-12-15 00:00:00
5           30         06/05/2008 2008-05-06 00:00:00

(5 row(s) affected)

*/

SELECT *
FROM dbo.ElencoFatture
WHERE data2 BETWEEN '20031201' AND '20031215';

/* Output:

numfattura  cliente    data       data2
----------- ---------- ---------- -----------------------
3           20         14/12/2003 2003-12-14 00:00:00
4           30         15/12/2003 2003-12-15 00:00:00

(2 row(s) affected)

*/

DROP TABLE dbo.ElencoFatture;