conversion failed datetime from charcter string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

conversion failed datetime from charcter string

Author

Topic

sateesh.sqldba
Starting Member

25 Posts

Posted - 2010-06-21 : 01:57:02

Hello every one,
i got problem conversion failed date time from character string when i was run query it is working fine but when i put it in stored procedure it is sending this error

Set @EndDateString = Cast((@MonthNumber) as varchar) + ''/'' + Cast(@DayBackOneWeekforReport as varchar) + ''/'' + Cast((Year(@TodaysDate)-1) as varchar) + '' 23:59:59.000''

--Print @EndDateString

Set @EndDate = cast(@EndDateString as datetime)

Error:conversion failed datetime from charcterstring
at line 75

Thank you In Advance

sateesh

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-21 : 02:01:32

post your error!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-21 : 02:04:06

put a length on varchar datatype

http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-21 : 03:01:44

It should be

Set @EndDateString = Cast((@MonthNumber) as varchar) + ''/'' + Cast(@CurrentDayforReport as varchar) + ''/'' + Cast(Year(@TodaysDate) as varchar) + '' 23:59:59.000''

But I think you can effeciently use
Set @EndDateString = dateadd(month,@MonthNumber-1,dateadd(year,@TodaysDate-1900,@CurrentDayforReport)

Set @EndDate = cast(@EndDateString as datetime)

and use it in where clause as

where date_col<@EndDate

Also make sure to read these
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-i.aspx
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-21 : 08:08:57

quote:
Originally posted by sateesh.sqldba

Hello every one,
i got problem conversion failed date time from character string when i was run query it is working fine but when i put it in stored procedure it is sending this error

Set @EndDateString = Cast((@MonthNumber) as varchar) + ''/'' + Cast(@DayBackOneWeekforReport as varchar) + ''/'' + Cast((Year(@TodaysDate)-1) as varchar) + '' 23:59:59.000''

--Print @EndDateString

Set @EndDate = cast(@EndDateString as datetime)

Error:conversion failed datetime from charcterstring
at line 75

Thank you In Advance

sateesh

What did you change?
Can you highlight it?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources