To all,

1. I have a table with 2 fields
      prdYear (Integer) - eg: value store (2007)
      prdMonth (Integer) - eg: value store (2)

2. The below statement working correctly

select Top 1 convert(datetime, cast(prdYear as varchar) + cast(prdMonth as varchar) +'01') from AgentProductionReport

3. But When i remove the 'Top 1', I getting this Error : Syntax error converting datetime from character string.

select convert(datetime, cast(prdYear as varchar) + cast(prdMonth as varchar) +'01') from AgentProductionReport


Can anyone tell me, why the above statement having a error!

Pls help, thanks in advanced!!!

Yes, I have checked your query, it's not working for some data. Change your query to :

select convert(datetime, cast(prdYear as varchar) + '-' + cast(prdMonth as varchar) + '-' + '01' )
from AgentProductionReport

Gaurav

1) CAST(... AS VARCHAR) returns only first character. CONVERT(VARCHAR, ...) returns all characters.
2) There is no ORDER BY, so the TOP 1 cannot be guaranteed.
3) This is more efficient

select Top 1 dateadd(month, 12 * prdYear - 22801 + prdMonth, 0) from AgentProductionReport
order by prdYear DESC, prdMonth DESC

select dateadd(month, 12 * prdYear - 22801 + prdMonth, 0) from AgentProductionReport
order by prdYear DESC, prdMonth DESC

Hi Peso,

I always see you posting for the datetime questions. You are good at it. If you wouldn't mind explaining your response, what exactly does the 22801 do? I'm just having the hardest time wrapping my head around the DATEADD function.

Thanks,
Richard

As the default year is 0 (1900-01-01), 22800 is the number of months (1900 * 12).
This way I still have the date 1900-01-01 as base date for further calculations.

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339 for further explanation.