Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
Hi Experts/Members, Need some advice on Case in SQL and IIF in Access, below are my scripts. I'm trying to convert to sql, I'm almost there but some of it is still wrong somehow, don't understand what causes this. Access:IIf(([DDate]-Date())>=0 And ([DDate]-Date())<7,"A",IIf(([DDate]-Date())>=7 And ([DDate]-Date())<14,"B",IIf(([DDate]-Date())>=14 And ([DDate]-Date())<21,"C",IIf(([DDate]-Date())>=21 And ([DDate]-Date())<28,"D"))))SQL: CASE WHEN(([DDate]-GETDate())>=0 And ([DDate]-GETDate())<7) THEN 'A'WHEN(([DDate]-GETDate())>=7 And ([DDate]-GETDate())<14) THEN 'B'WHEN(([DDate]-GETDate())>=14 And ([DDate]-GETDate())<21) THEN 'C'WHEN(([DDate]-GETDate())>=21 And ([DDate]-GETDate())<28) THEN 'D'ENDFor e.g. DDate =9/19/2013[DDate]-Getdate() = 16 Access returns --> DSql returns --> CAppreciate if anyone can advice on this
bandi
Master Smack Fu Yak Hacker
2242 Posts
Posted - 2013-09-03 : 03:16:33
--use DATEDIFF() in the SQL Server
CASE WHEN (DATEDIFF(DD, GETDATE(), [DDate]) BETWEEN 0 And 6) THEN 'A'WHEN(DATEDIFF(DD, GETDATE(), [DDate]) BETWEEN 7 And 13) THEN 'B'WHEN(DATEDIFF(DD, GETDATE(), [DDate]) BETWEEN 14 And 20) THEN 'C'WHEN(DATEDIFF(DD, GETDATE(), [DDate]) BETWEEN 21 And 27) THEN 'D'END
--Chandu
theKid27
Starting Member
21 Posts
Posted - 2013-09-03 : 03:35:24
Thank bandi appreciate for the help.. i get the trick
bandi
Master Smack Fu Yak Hacker
2242 Posts
Posted - 2013-09-03 : 05:28:41
quote:Originally posted by theKid27 Thank bandi appreciate for the help.. i get the trick
Welcome--Chandu
visakh16
Very Important crosS Applying yaK Herder
52326 Posts
Posted - 2013-09-03 : 09:31:25
you could simply simplify it as
CHAR((DATEDIFF(DD, GETDATE(), [DDate])/7) + 65)
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs