Problem with Now function - 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

General SQL Server Forums

New to SQL Server Programming

Problem with Now function

Author

Topic

hollyquinn
Starting Member

31 Posts

Posted - 2008-10-23 : 10:57:31

Hi I am hoping someone can help me with this. I am trying to create a Stored procedure in SQL Server 2005 and it keeps getting hung up on the following line of the code:

WHERE (((tblSessionID.SessionMonth) Between Now() - 365 And Now()+ 395)

I read that I should replace Now() with GetDate(), but that doesn't work either. I get incorrect syntax with both lines. Any idea?

I am trying to convert a SQL statement from an Access database. I'm new to SQL Server and don't know how to get this to work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 11:00:52

WHERE ((tblSessionID.SessionMonth) Between DATEADD(dd,- 365,GETDATE()) And DATEADD(dd,395,GETDATE()))

hollyquinn
Starting Member

31 Posts

Posted - 2008-10-23 : 11:10:44

quote:
Originally posted by visakh16

WHERE ((tblSessionID.SessionMonth) Between DATEADD(dd,- 365,GETDATE()) And DATEADD(dd,395,GETDATE()))

Ok now I'm getting all kinds of syntax errors. Let me post the entire thing with your line added. If you don't mind to take a look?

SELECT tblSessionID.SessionID, tblSessionID.DateIssued, tblSessionID.SessionMonth, tblSessionID.SessionType, tblSessionID.Program, tblSessionID.AircraftType, tblSessionID.ScenarioID, tblSessionID.PilotEvaltrID, tblSessionID.SOEvaltrID, tblSessionID.IssuedCA, tblSessionID.IssuedFO, tblSessionID.IssuedSO, tblSessionID.IsVoid, tblSessionID.DCFPRINTED, tblSessionID.CREATEDBY, tblSession.CARandomID, tblSession.FORandomID, tblSession.SORandomID, tblSession.SessionID, tblSessionID.CAParticipant, tblSessionID.FOParticipant, tblSessionID.SOParticipant, tlkpScenarioID.Description, tblSessionID.MinLevel, tblSessionID.FLTNbr, tblSessionID.FltZDate
FROM (tblSessionID LEFT JOIN tblSession ON tblSessionID.SessionID = tblSession.SessionID) LEFT JOIN tlkpScenarioID ON tblSessionID.ScenarioID = tlkpScenarioID.ScenarioID
WHERE ((tblSessionID.SessionMonth) Between DATEADD(dd,- 365,GETDATE()) And DATEADD(dd,395,GETDATE())) AND ((tblSessionID.IssuedFO)=True) AND ((tblSessionID.IsVoid)=False) AND ((tblSession.FORandomID) Is Null))
OR ((tblSessionID.SessionMonth) Between DATEADD(dd,- 365,GETDATE()) And DATEADD(dd,395,GETDATE())) AND ((tblSessionID.IssuedSO)=True) AND ((tblSessionID.IsVoid)=False) AND ((tblSession.SORandomID) Is Null))
ORDER BY tblSessionID.SessionID DESC;

Thank you.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 11:14:09

Please specify the error, Sql 2000 or 2005 or access..

also perhaps the DDL for these tables.

hollyquinn
Starting Member

31 Posts

Posted - 2008-10-23 : 11:17:16

quote:
Originally posted by hanbingl

Please specify the error, Sql 2000 or 2005 or access..

also perhaps the DDL for these tables.

It's SQL Server 2005. This is the error message I'm getting

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-10-23 : 11:17:17

1) Check your parenthesis to make sure they line up. remove any and all that you don't need.

2) "TRUE" is not a valid literal in SQL Server; booleans data is stored using the BIT data type which has values of 1 or 0.

More on converting Access to SQL Server here:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

hollyquinn
Starting Member

31 Posts

Posted - 2008-10-23 : 11:19:03

quote:
Originally posted by jsmith8858

1) Check your parenthesis to make sure they line up. remove any and all that you don't need.

2) "TRUE" is not a valid literal in SQL Server; booleans data is stored using the BIT data type which has values of 1 or 0.

More on converting Access to SQL Server here:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Thanks Jeff! That is a great link. I will check my parenthesis to see if I've made a mistake. That link will come in handy for sure.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-10-23 : 11:19:27

Here's my take:


WHERE 
      (
            tblSessionID.SessionMonth  Between DATEADD(dd,- 365,GETDATE()) And DATEADD(dd,395,GETDATE())
		   
			  AND  tblSessionID.IssuedFO='True' 
			  AND tblSessionID.IsVoid='False' 
			  AND tblSession.FORandomID Is Null
      ) 
OR (
     tblSessionID.SessionMonth Between DATEADD(dd,- 365,GETDATE()) And DATEADD(dd,395,GETDATE())) 
      AND tblSessionID.IssuedSO = 'True' 
      AND tblSessionID.IsVoid   = 'False' 
      AND tblSession.SORandomID Is Null
    )

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-23 : 11:23:01

[code]SELECT tblSessionID.SessionID,
tblSessionID.DateIssued,
tblSessionID.SessionMonth,
tblSessionID.SessionType,
tblSessionID.Program,
tblSessionID.AircraftType,
tblSessionID.ScenarioID,
tblSessionID.PilotEvaltrID,
tblSessionID.SOEvaltrID,
tblSessionID.IssuedCA,
tblSessionID.IssuedFO,
tblSessionID.IssuedSO,
tblSessionID.IsVoid,
tblSessionID.DCFPRINTED,
tblSessionID.CREATEDBY,
tblSession.CARandomID,
tblSession.FORandomID,
tblSession.SORandomID,
tblSession.SessionID,
tblSessionID.CAParticipant,
tblSessionID.FOParticipant,
tblSessionID.SOParticipant,
tlkpScenarioID.Description,
tblSessionID.MinLevel,
tblSessionID.FLTNbr,
tblSessionID.FltZDate
FROM tblSessionID
LEFT JOIN tblSession ON tblSession.SessionID = tblSessionID.SessionID
LEFT JOIN tlkpScenarioID ON tlkpScenarioID.ScenarioID = tblSessionID.ScenarioID
WHERE tblSessionID.SessionMonth BETWEEN DATEADD(dd, -365, GETDATE()) AND DATEADD(dd, 395, GETDATE())
AND tblSessionID.IsVoid = 0
AND 1 IN (tblSessionID.IssuedFO, tblSessionID.IssuedSO)
AND (tblSession.FORandomID IS NULL OR tblSession.SORandomID IS NULL)
ORDER BY tblSessionID.SessionID DESC[/code]

E 12°55'05.63"
N 56°04'39.26"

hollyquinn
Starting Member

31 Posts

Posted - 2008-10-23 : 11:24:19

quote:

Originally posted by jimf

Here's my take:


WHERE 
      (
            tblSessionID.SessionMonth  Between DATEADD(dd,- 365,GETDATE()) And DATEADD(dd,395,GETDATE())
		   
			  AND  tblSessionID.IssuedFO='True' 
			  AND tblSessionID.IsVoid='False' 
			  AND tblSession.FORandomID Is Null
      ) 
OR (
     tblSessionID.SessionMonth Between DATEADD(dd,- 365,GETDATE()) And DATEADD(dd,395,GETDATE())) 
      AND tblSessionID.IssuedSO = 'True' 
      AND tblSessionID.IsVoid   = 'False' 
      AND tblSession.SORandomID Is Null
    )

Jim

Sweet it works after I remove a couple of the parethisis. You rock. Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-10-23 : 11:34:10

You're welcome!

This is a bit cleaner, and more set-based


WHERE
      tblSessionID.SessionMonth Between DATEADD(dd,- 365,GETDATE()) And DATEADD(dd,395,GETDATE()) 
  and tblSessionID.IsVoid   = 'False' 
  and
      (    (tblSessionID.IssuedFO = 'True' and tblSession.FORandomID Is Null )
       OR   tblSessionID.IssuedSO = 'True' and tblSession.SORandomID Is Null )            
      )

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources