Error With Date - Conversion Failed

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 2000 Forums

SQL Server Development (2000)

Error With Date - Conversion Failed

Author

Topic

newone
Starting Member

3 Posts

Posted - 2006-11-14 : 10:59:03

I have a dynamic sql query

Select @sql = 'SELECT DISTINCT top 1000

Dateadd(ss,a.PEntered,''01-Jan-1970 02:00:00'') as ''Date Entered''

,a.PNumber as ''SR Number''

,c.DictKey1 as ''Log Method''

,e.[Name] as ''Logged BY''

,a.PContCmpy as ''Company''

,b.Descrip as ''Site''

,d.AgreeNo as ''Agreement Owner''

,g.[Name]

,g.Assgroup

FROM dbo.SUPPORT_TPROBLEM as a with (nolock)

inner join dbo.CONTACT_SITE as b on b.[Name]= a.PSite

inner join dbo.PURSUIT_TDICT6 as c with (nolock) on c.DictSNo = a.PDict6

inner join dbo.TEST1 as d with (nolock) on d.ReqNum = a.PNumber

inner join dbo.TEST2 as e with (nolock) on e.Mkey = a.pLogdBy

inner join dbo.TEST3 as f with (nolock) on f.Mkey = a.PContact

inner join dbo.TEST4 as g with (nolock) on g.Mkey = a.PFixBy

where a.PFixBy = g.Mkey '

IF( @DE_SDate <> @DE_EDate)

BEGIN

Select @sql = @sql + ' and Dateadd(ss,a.PEntered,''01-Jan-1970 02:00:00'') between '+ @DE_SDate + ' and ' + @DE_EDate

END

IF( @DF_SDate <> @DF_EDate)

BEGIN

Select @sql = @sql + ' and Dateadd(ss,a.PFixAt,''01-Jan-1970 02:00:00'') between '+ @DF_SDate + ' and ' + @DF_EDate

END

It gives me an error when I have dates selected "error conversion failed when converting character string to datetime".

When I remove it from the dynamic sql and have it as just a normal query it works fine, am I missing something, been sitting with this for hours it's annoying me. Both date fields in the table are declared as varchar(50).

PLEASE HELP ?????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 11:05:10

Yes. DATEADD function must have dates with single quote, not double quote.

DATEADD(ss, a.PFixAt, '01-Jan-1970 02:00:00')

Peter Larsson
Helsingborg, Sweden

newone
Starting Member

3 Posts

Posted - 2006-11-14 : 11:17:31

Hi Peter
Thank you for your response, I certianly do appreciate it.
However i am using that dateadd function in a Dynamic Sql Query
So i need to have the double quote?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 11:20:46

If you add a PRINT @sql last in the code, and post the outcome here, we'll take a look at that.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 11:25:07

It is the IF statements that fails. BETWEEN needs dates with single quotes too.
Try this

Select @sql = 'SELECT DISTINCT top 1000

Dateadd(ss,a.PEntered,''01-Jan-1970 02:00:00'') as [Date Entered]
,a.PNumber as [SR Number]
,c.DictKey1 as [Log Method]
,e.[Name] as [Logged BY]
,a.PContCmpy as [Company]
,b.Descrip as [Site]
,d.AgreeNo as [Agreement Owner]
,g.[Name] 
,g.Assgroup
FROM dbo.SUPPORT_TPROBLEM as a with (nolock) 
inner join dbo.CONTACT_SITE as b on b.[Name]= a.PSite 
inner join dbo.PURSUIT_TDICT6 as c with (nolock) on c.DictSNo = a.PDict6
inner join dbo.TEST1 as d with (nolock) on d.ReqNum = a.PNumber
inner join dbo.TEST2 as e with (nolock) on e.Mkey = a.pLogdBy
inner join dbo.TEST3 as f with (nolock) on f.Mkey = a.PContact 
inner join dbo.TEST4 as g with (nolock) on g.Mkey = a.PFixBy 
where a.PFixBy = g.Mkey '


IF( @DE_SDate <> @DE_EDate)
	Select @sql = @sql + ' and Dateadd(ss,a.PEntered,''01-Jan-1970 02:00:00'') between ''' + @DE_SDate + ''' and ''' + @DE_EDate + ''''

IF( @DF_SDate <> @DF_EDate)
	Select @sql = @sql + ' and Dateadd(ss,a.PFixAt,''01-Jan-1970 02:00:00'') between ''' + @DF_SDate + ''' and ''' + @DF_EDate + ''''

Peter Larsson
Helsingborg, Sweden

newone
Starting Member

3 Posts

Posted - 2006-11-14 : 11:39:28

Hello,

I tried your suggestion but it's still gives me the same error and i does not PRINT the sql. SO i changed it a bit
like :
IF( @DE_SDate <> @DE_EDate)
Select @sql = @sql + ' and Dateadd(ss,a.PEntered,''01-Jan-1970 02:00:00'') between '''' + @DE_SDate + '''' and '''' + @DE_EDate + '''''

and it prints this

SELECT DISTINCT top 1000
Dateadd(ss,a.PEntered,'01-Jan-1970 02:00:00') as 'Date Entered'
,a.PNumber as 'SR Number'
,c.DictKey1 as 'Log Method'
,e.[Name] as 'Logged BY'
,a.PContCmpy as 'Company'
,b.Descrip as 'Site'
,d.AgreeNo as 'Agreement Owner'
,g.[Name]
,g.Assgroup
FROM dbo.SUPPORT_TPROBLEM as a with (nolock)
inner join dbo.CONTACT_SITE as b on b.[Name]= a.PSite
inner join dbo.PURSUIT_TDICT6 as c with (nolock) on c.DictSNo = a.PDict6
inner join dbo.SUPPORT_AGAUDT as d with (nolock) on d.ReqNum = a.PNumber
inner join dbo.CONTACT_CONTACT as e with (nolock) on e.Mkey = a.pLogdBy
inner join dbo.CONTACT_CONTACT as f with (nolock) on f.Mkey = a.PContact
inner join dbo.CONTACT_CONTACT as g with (nolock) on g.Mkey = a.PFixBy
where a.PFixBy = g.Mkey and Dateadd(ss,a.PEntered,'01-Jan-1970 02:00:00') between '' + @DE_SDate + '' and '' + @DE_EDate + ''

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 11:46:04

Too many quotes. Make sure there are just 3 quotes and 4 quotes last.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-15 : 00:13:26

[code].
.
.
where a.PFixBy = g.Mkey
and Dateadd(ss,a.PEntered,'01-Jan-1970 02:00:00')
between ''' + @DE_SDate + ''' and ''' + @DE_EDate + ''''[/code]

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-15 : 07:45:27

When you are not passing object names as parameters, what is the need for Dynamic SQL?
Read this fully
www.Sommarskog.se/Dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources