I have thid query,

SELECT DISTINCT DATEPART(year, dateprinted) +Convert(VarChar(20), dateprinted, 103) AS Year
FROM tbl_pcounterlog
ORDER BY Year

BWhen I pars it it says that it is okay yet when I run it I get the following error,

Syntax error converting the varchar value '01/02/2007 08:11' to a column of data type int.

What am I doing wrong

Thanks

Greg

DATEPART returns an INT and has no implicit conversion to the VARCHAR in your CONVERT... change DATEPART to DATENAME and that problem will go away.

--Jeff Moden

Thanks Jeff

Changing to DATENAME does not work either.

Here is the senareo. I have an app that captures all printing info and when I import the apps log file into a sql table (to creat reports using RS 200) the date is imported into a column which has a data type of varchar (20). I am wanting to write this query in my RS report.

[red][red]SELECT DISTINCT DATENAME (year, dateprinted) +Convert(VarChar(20), dateprinted, 103) AS Year
FROM tbl_pcounterlog
ORDER BY Year

Is there a better war to do this - like importing the data into a temp table and then using a SP to copy date and change the date format to DateTime and then query the table with:

SELECT DISTINCT DATEPART (year, dateprinted) AS Year
FROM tbl_pcounterlog
ORDER BY Year

Thans

Greg

How do you import data from log file into sql table? Do you use DTS package or BCP utility? With BCP or BULK INSERT you can have a format file defined that is referenced by the BCP utility while importing data and you can avoid storing it into a temp table and so on. It also depends on how the data is stored into the log file from which you are importing data.

Cheers

_________________________
- Vijay G
Strive for Perfection

Ah... I get it... of course it would be better to have dates in DATETIME columns but I believe the following will do what you want...

SELECT DISTINCT DATEPART (year, CONVERT(DATETIME,dateprinted,103)) AS Year
FROM tbl_pcounterlog
ORDER BY Year


--Jeff Moden