[Resolved] Comparing Dates - SQL Server Forums

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)

[Resolved] Comparing Dates

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-03-16 : 11:25:13

I am trying to do an openquery against a db2/400 table and need to compare and select based upon dates.



declare @DateFrom datetime
declare @DateTo datetime 
declare @SQLSTRING varchar(500)

set @DateFrom = DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) 
set @DateTo = DATEADD(MONTH, 1, @DateFrom)

SET @SQLSTRING = 
'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
''select    wapro as product,
           sum(cast(wagrot as decimal(15,2)) / 100) as qty
           from       VGIWRQLIB.WRQASI
where      watrn@ >= ''''' + @DateFrom + ''''' and
           watrn@ <= ''''' + @DateTo + '''''  
group by   wapro
fetch First 3 Rows Only'')'

exec (@SQLSTRING)

I am getting previous month start and end date

Feb 1 2010 12:00AM
Mar 1 2010 12:00AM

and need to compare from/ to date to date in the db2/400 (field watrn@)table which is 10 character long with format 03/15/10.

Right now I am getting error:

quote:
Conversion failed when converting datetime from character string.

Can somebody help please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:30:12

make set as


SET @SQLSTRING = 
'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
''select    wapro as product,
           sum(cast(wagrot as decimal(15,2)) / 100) as qty
           from       VGIWRQLIB.WRQASI
where      watrn@ >= ''''' + CONVERT(varchar(11),@DateFrom,121) + ''''' and
           watrn@ <= ''''' + CONVERT(varchar(11),@DateTo,121) + '''''  
group by   wapro
fetch First 3 Rows Only'')'

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

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-03-16 : 11:36:53

Hi visakh16,

I'm impressed. Worked great.

Do you have tell me what you are doing? Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:39:34

sure..You were trying to concatenate datetime variable to string part which threw the error. i convert date also to varchar for concatenation to happen smoothly

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-17 : 03:06:35

See if it works without dynamic SQL


SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
'select    wapro as product,
           sum(cast(wagrot as decimal(15,2)) / 100) as qty
           from       VGIWRQLIB.WRQASI
where      watrn@ >= '''+@DateFrom+'''  and
           watrn@ <= ''' + @DateTo + '''
group by   wapro
fetch First 3 Rows Only')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources