Conversion from string" "to type 'Date' not valid

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

General SQL Server Forums

New to SQL Server Programming

Conversion from string" "to type 'Date' not valid

Author

Topic

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2010-04-11 : 06:25:57

I am using the following expression in my textbox25
=

"ALERTS-"&SUM(Iif("ALERT" =(Switch(Isnothing(Fields!Materials.Value),"",

Fields!Materials.Value = "","",

Fields!Materials.Value = " ","",

Fields!Materials.Value <>

"" OR Fields!Materials.Value <> " " OR NOT IsNothing(Fields!Materials.Value),

Iif(Fields!ProofsDue.Value >= (

Switch(Isnothing(Fields!Proof.Value),Now,

Fields!Proof.Value =

"",Now,

Fields!Proof.Value =

" ",Now,

Fields!Proof.Value <>

"" OR Fields!Proof.Value <> " " OR NOT IsNothing(Fields!Proof.Value),Fields!Proof.Value))

,

"","ALERT")

)),1,0))

And following is the query i am using:

SELECT CallLog.CallID, CallLog.RecvdDate, Subset.CustID, Detail.CampaignName, Asgnmnt.GroupName, CallLog.CallStatus, CallLog.Priority,

CONVERT(varchar(20), Detail.MaterialsDate + ' ' + Detail.MaterialsTime, 120) AS Materials,
CONVERT(varchar(20), Detail.ProofDate + ' ' + Detail.ProofTime, 120) AS Proof,
CONVERT(varchar(20), Detail.Date2 + ' ' + Detail.Time2, 120) AS ProofsDue, -------------------

------------------------------

Kristen
Test

22859 Posts

Posted - 2010-04-11 : 06:34:06

"Conversion from string" "to type 'Date' not valid"

OK, but your code:


CONVERT(varchar(20), Detail.MaterialsDate + ' ' + Detail.MaterialsTime, 120) AS Materials

is converting from Date to String (in format "yyyy-mm-dd hh:mi:ss")

If Detail.MaterialsDate is a string in "yyyy-mm-dd" format and Detail.MaterialsTime astring in "hh:mi:ss" then you need


CONVERT(datetime, Detail.MaterialsDate + ' ' + Detail.MaterialsTime, 120) AS Materials

to convert it to a Date Time datatype.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-11 : 11:47:53

actually you dont require convertion to varchar at all. you can bring it as datetime itself and do convertion using formatting functions in SSRS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2010-04-12 : 09:36:49

I am using FormatDateTime(Now) and it is working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources