help with where clause - 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)

help with where clause

Author

Topic

lcsgeek
Starting Member

38 Posts

Posted - 2009-10-22 : 12:59:43

I have a table which tracks school year terms. Here are a few of the columns:

termId identity
startDate nvarchar(8) 'format yyyymmdd
endDate nvarchar(8) 'format yyyymmdd

I've written a user defined function which returns the current date in yyymmdd format into @currDate. What I want to do now is query the tblTerms table and harvest the termId where @currDate is between startDate and endDate. This seems simple enough but it escapes me. Any help would be appreciated.

-d

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 13:04:31

Select * from table where @currDate between startDate and endDate?
will that work try it

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-22 : 13:11:37

quote:
Originally posted by lcsgeek

I have a table which tracks school year terms. Here are a few of the columns:

termId identity
startDate nvarchar(8) 'format yyyymmdd
endDate nvarchar(8) 'format yyyymmdd

I've written a user defined function which returns the current date in yyymmdd format into @currDate. What I want to do now is query the tblTerms table and harvest the termId where @currDate is between startDate and endDate. This seems simple enough but it escapes me. Any help would be appreciated.

-d

why are you using nvarchar for storing dates? you should always try to use proper datatype for your fields

lcsgeek
Starting Member

38 Posts

Posted - 2009-10-22 : 14:17:52

Sorry to bother you, this is really simple.

SELECT termId, termYear, termYearText, termNumber, actualStartDate, actualEndDate, nominalStartDate, nominalEndDate
FROM tblTerms
WHERE (nominalStartDate <= @currentGtDate) AND (nominalEndDate >= @currentGtDate)

gaauspawcscwcj
Starting Member

29 Posts

Posted - 2009-10-23 : 00:05:03

use between instead of WHERE (nominalStartDate <= @currentGtDate) AND (nominalEndDate >= @currentGtDate)

gaauspawcscwcj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 12:55:27

quote:
Originally posted by lcsgeek

Sorry to bother you, this is really simple.

SELECT termId, termYear, termYearText, termNumber, actualStartDate, actualEndDate, nominalStartDate, nominalEndDate
FROM tblTerms
WHERE (nominalStartDate <= @currentGtDate) AND (nominalEndDate >= @currentGtDate)

will this work fine if field datatype is nvarchar?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources