trying to covert an Ms Access query to Tsql

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

Transact-SQL (2000)

trying to covert an Ms Access query to Tsql

Author

Topic

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2009-05-07 : 13:11:25

Hi,
I am trying to convert this to its wequivelnt in a stored procedure, but I keep getting an erroe message sysntext error near Then:

here is the Access code:

((IIf(DateAdd("d",-1,DateAdd("m",-2,[Forms]![Switchboard]![Monthly].[Form]![Month] & "/01/" & [Forms]![Switchboard]![Monthly].[Form]![year])) Between [Grp_Start_Date] And IIf(IsNull([Grp_End_Date]),Now(),[Grp_End_Date]),1,0))=1))

Here is my attemped in SQL server:
((Case when DateAdd(day,-1,DateAdd(month,-2,@Month + '/01/' + @Year)) Between [Grp_Start_Date]
And Case when(IsNull([Grp_End_Date],GetDate()) Then 1 Else 0 End) = 1) Else Null END)

Any ideas would really be appreciated
thank you

ITM

seldenm
Starting Member

5 Posts

Posted - 2009-05-08 : 16:32:46

Here is some SQL code that may help. I've replaced [Grp_End_Date] and [Grp_Start_Date] with local variables so you can run this in the query analyzer without any particular data. Also, I replaced your case statement to deal with NULL values of Grp_End_Date with a more concise COALESCE statement. Let me know if any of this is not clear.

Also, if your @Month and @Year are declared as INT, you will have some conversion problems, but I'm assuming they are string values.

Good luck!

DECLARE @Month VARCHAR(2)
DECLARE @Year VARCHAR(4)
DECLARE @Grp_End_Date DATETIME
DECLARE @Grp_Start_Date DATETIME

SET @Month = '05'
SET @Year = '2009'
SET @Grp_End_Date = '6/3/2009'
SET @Grp_Start_Date = '2/1/2009'

select Case when
DateAdd(day,-1,DateAdd(month,-2,@Month + '/01/' + @Year))
Between @Grp_Start_Date And COALESCE(@Grp_End_Date,GetDate()) THEN 1 ELSE NULL END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 05:25:23

if they are integer, you can use like this


select Case when 
DateAdd(dd,-1,DateAdd(mm,@month-3,DATEADD(yy,@year-1900,0))) 
Between @Grp_Start_Date And COALESCE(@Grp_End_Date,GetDate()) THEN 1 ELSE NULL END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources