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.
| Author |
Topic |
|
kprasadreddy
Starting Member
41 Posts |
 Posted - 2004-11-17 : 11:08:59
|
| I am trying to get a date usingCdate(IIf(Day(getDate() >'15'),(Month(getDate())+1) & '/1/' & Year(getDate()),(Month(getDate())) & '/1/' & Year(getDate()))) Looking to get a data starting next month i.e is 12/01/2004 if day > 15 and 11/01/2004 if day < 15.Any help is appreciated....SELECT COUNT(STDNT_NB) as Ldr_Basic, LDV180.LOC_CD FROM ZLDV180_STDNT_PASSING LDV180WHERE LDV180.CRRCLM_CD = 4AND LDV180.LOC_CD IN ('BA','CS','ED','GR','HE','IR','NA','TA')AND LDV180.STDNT_EMPLR_CD IN (1,99)AND LDV180.SCR_DT < Cdate(IIf(Day(getDate() >'15'),(Month(getDate())+1) & '/1/' & Year(getDate()),(Month(getDate())) & '/1/' & Year(getDate()))) AND LDV180.CRS_NB IN(SELECT CRS_NB FROM ZLDT075_CRRCLM_CRS WHERE CRRCLM_CD =9 AND CRRCLM_TRACK_CD =80)GROUP BY LDV180.LOC_CD |
|
|
kprasadreddy
Starting Member
41 Posts |
Posted - 2004-11-17 : 14:16:57
|
| How do I get the first day of next month like 12/01/2004 |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-17 : 14:25:47
|
| Is this an Access question or a SQL Server question? Doublecheck the various forums available here at SQLTeam to make sure you are asking your question in the right place.- Jeff |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-17 : 20:49:22
|
| there's no getdate() in access but no iif and cdate in sql, hmm...i think, it's a conversion from access to sql?...and ldv180.scr_dt < case when day(getdate())>15 then datediff(day,day(getdate())-1,dateadd(month,1,getdate())) else dateadd(month,1,getdate()) end--------------------keeping it simple... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-17 : 21:11:14
|
| GetDate() in SQL Server = Now() in MS Access. It would be better to use the Date() function in Access if you don't want the time portion.First day of next month:SQL Server: SELECT DateAdd(m, DateDiff(m, '1/1/1900', GetDate()) + 1, '1/1/1900')MS Access: SELECT DateAdd("m",DateDiff("m",#1/1/1900#,Date())+1,#1/1/1900#)P.S. IIf() in Access can be accomplished using the CASE expression in SQL Server. CDate() can be done using CONVERT(). See SQL Server Books Online for more details. |
|
|
|
|
|
|
|
|
Date Issue