Datepart problem

try

datepart(month, cast(dbo.sales.[Date] as datetime))

opps there's another problem too ... month is not right ... try this

datepart(mm, cast(dbo.sales.[Date] as datetime))

Thanks volking. I get the same error however.

I tried the second solution and get the same problem.

you must have a problem with one of the values in the date column that is not able to be converted to a datetime data type.

Can you send us the complete error that you are getting so that we can assist?

I should mention I am using the application Business Objects to enter custom SQL. The error the application gives it "invalid type of column (1)".

have you tried doing the following?

cast(datepart(mm, cast(dbo.sales.[Date] as datetime))as int)

or

cast(datepart(mm, cast(dbo.sales.[Date] as datetime)) as varchar(10))

it looks like busines objects custom sql has to to make some modification to the code it has inputted, so it may not like the way this field is setup...

if you cannot get either of these to work, you may want to try creating a view off of this table that has something like the following

CREATE VIEW dbo.v_sales
AS
SELECT *, DATEPART(mm, CAST([date] as datetime)) AS [date_month] from dbo.sales

and then use this table in your custom sql to select from...

the one other thing you could try before doing a view, is that the column may need a name... i'm not sure as i don't work with business objects so just trying to think through all of the possibilities...

cast(datepart(mm, cast(dbo.sales.[Date] as datetime))as int) AS [Date_Month]

Thanks again but nothing changes. Still stuck.

Is this query part of a subquery? Where as the data in the parent query is looking for the column to be of a certain datatype?

lets try declaring EVERYTHING - this works and returns [10]
select cast(datepart(mm,cast('10/10/2011' as datetime)) as int)

so if we substitute dbo.sales.[Date]

then this SHOULD work too
select cast(datepart(mm,cast(dbo.sales.[Date] as datetime)) as int)

I have a sneaking thought, maybe we're looking at the WRONG problem.
leeskelton83 ... would you post the ENTIRE SQL you're using?

One more dumb question, the table dbo.sales DOES have a column named [Date] right?

Here you are. Some of the fields are different because I wanted to simplify my post but nonetheless..

SELECT
  cast(datepart(mm, cast(dbo.FPS_DailySales.[Date] as datetime)) as varchar(10))

FROM
  dbo.FPS_DailySales

Damn Dude ... there's NOTHING WRONG with that SQL ...

maybe try reducing the specificity

SELECT
  cast(datepart(mm, cast([Date] as datetime)) as varchar(10))  

FROM
  dbo.FPS_DailySales

and I'll ask again ...
 the table dbo.sales DOES have a column named [Date] right?

Well. it's got me stumped

The other thing about this is does this populate something in a report, or something like that where whatever is getting populated is expecting "x" date type, and you're giving it "y" ?

Ummmmm I just checked and I don't think Business Objects has a CAST function. They use CONVERT.
Let me keep reading.

I'm reading
http://www.lingnan.net/lab/uploadfile/200864183321539.pdf

I'm reading PAGE 59

Nothing yet.

Thanks for all your help volking. Heading out for today and will check in tomorrow.

Great effort though I found no solution