'DATE' is not a recognized function name.

[Finedean]

Hi,
I am new to SQL server programming.

the following code works without a problem:

USE MIS
select PROV_MASTERS_HCVW.PROVID AS [SITE NAME],(PROV_MASTERS_HCVW.LASTNAME +','+ PROV_MASTERS_HCVW.FIRSTNAME)AS FULLNAME,
PROV_MASTERS_HCVW.BIRTHDATE, PROV_MASTERS_HCVW.CLASS, PROV_MASTERS_HCVW.CONTRACT,PROV_MASTERS_HCVW.TERMDATE,

PROV_SPECINFO_HCVW.SPECCODE, PROV_SPECINFO_HCVW.TYPE,

PROV_SPECCODES_VS.CODE, PROV_SPECCODES_VS.DESCR


FROM ((PROV_MASTERS_HCVW INNER JOIN PROV_SPECINFO_HCVW ON PROV_MASTERS_HCVW.PROVID = PROV_SPECINFO_HCVW.PROVID)INNER JOIN
PROV_SPECCODES_VS ON PROV_SPECINFO_HCVW.SPECCODE = PROV_SPECCODES_VS.CODE)

WHERE (PROV_MASTERS_HCVW.CLASS IN('A', 'H'))AND (PROV_MASTERS_HCVW.CONTRACT IN ('1', '4')) AND
(PROV_SPECCODES_VS.CODE = 'DY') AND (PROV_SPECINFO_HCVW.TYPE ='PRIMARY')AND (PROV_MASTERS_HCVW.TERMDATE)is null

But once I add this line:
or (PROV_MASTERS_HCVW.TERMDATE) > DATE())

I get the following error:
'DATE' is not a recognized function name.

What am I doing wrong?

Thanks
Dean

 

[thendrickson]

GetDate() returns current server date time
Take a look at BOL when you have time

 

[Finedean]

Thank you, I will look into it.

 

[thendrickson]

Dates can be a bit tricky at times.

There are many posts in these forums as well as some FAQS that can be valueable.

BOL is the first place to look though

Since you are new to SQL, I will also point out a common mistake I have often found in T-SQL.


Select GETDATE() just returned the value
'2006-07-31 12:52:57.420'


Your code (if I understand your intention) would find any records where the date = '2006-07-31 12:52:57.421' (if you use > GetDate()) and later

Is that within the range of values you actually wish to find?

Just remember = will almost never actually be =

You need to account for the differences within your T-SQL

I often use the DATEDIFF function and check for 0 to determine equality.
DateDiff(dd etc
will return records based on the current day if 0, hh from the same hour etc. How close to you need it?

There are many other ways to compare dates as well.
Look over the functions in BOL then search this forum again to clear up any confusion

 

[Finedean]

I'll do. Thank you very much.
Dean