Datepart as part of Select Statement

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 2000 Forums

SQL Server Development (2000)

Datepart as part of Select Statement

Author

Topic

NMCoderMike
Starting Member

4 Posts

Posted - 2006-01-16 : 11:54:16

A litle explanation of what I am doing, I need to be able to search by year, the issue is the year is within a column that is smalldate, so I have to extract the Year portion and then match

if I put this into Query Analyzer
Select * From WalkinInfo Where DatePart(YYYY,Datein) = '2004'

I have no problems, it selects all the records from 2004. Below is the statement I am using in the actual code.

"SELECT * FROM dbo.WalkinInfo WHERE " & DATEPART(YYYY,Datein) & "=" & "'" & walkinInfo__MMColParamDate & "'" & ""

This throws up all over itself when I run it, the error I get is "Error Type:Microsoft VBScript runtime (0x800A0005)
Invalid procedure call or argument: 'DATEPART'"

Can anyone help out?
Thx.

Kristen
Test

22859 Posts

Posted - 2006-01-16 : 12:07:18

Hi NMCoderMike, Welcome to SQL Team!

Did you perhaps mean to use:

"SELECT * FROM dbo.WalkinInfo WHERE DATEPART(YYYY,Datein) = '" & walkinInfo__MMColParamDate & "'"

Kristen

NMCoderMike
Starting Member

4 Posts

Posted - 2006-01-16 : 12:59:47

Thanks & it's nice to be here, that doesn't throw any errors, still won't return the records, but that's an issue I will have to work out.

NMCoderMike
Starting Member

4 Posts

Posted - 2006-01-16 : 13:37:41

Great gotit to work, had to rework the input variables.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-17 : 01:25:38

Kris, will DatePart take advantage over Year by making use of index if any?

"SELECT * FROM dbo.WalkinInfo WHERE Year(Datein) = '" & walkinInfo__MMColParamDate & "'"

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-17 : 01:36:53

"will DatePart take advantage over Year by making use of index if any?"

I toyed with answering that, and was in a rush so I just pressed SUBMIT and hoped you wouldn't notice!!!!

NMCoderMike: You would be better off with:


SELECT * 
FROM dbo.WalkinInfo 
WHERE     Datein >= '... 1st January of appropriate year'
      AND Datein <  '... 1st January of following year'

because this way the actual column, "Datein", is not part of an expression. Thus if "Datein" is in an index SQL Server can make use of the index very efficiently, but with an expression on "Datein" then SQL Server will either not use the index at all, or use ALL the index and perform the calculation on it trying to find the appropriate values.

So for example:


SELECT * 
FROM dbo.WalkinInfo 
WHERE     Datein >= '20050101'
      AND Datein <  '20060101'

Points to note:

Use "Datein < '20060101'" rather than "Datein <= '20051231'" because this will cover the situation where "Datein" contains a time, as well as a date.

If you are passing the date to SQL Server as a Text String always use the 'yyyymmdd' format (NO hyphens etc.) Better still pass it from your application using a native Date or DateTime datatype variable in your application - ADO can then take care of passing the date in "native" format.

If you only have a Year to compare against then you either need to make a 'yyyymmdd' string (or DateTime variable) out of it, or get SQL to do the calculation for you:


"SELECT * 
FROM dbo.WalkinInfo 
WHERE     Datein >= DATEADD(Year, " & walkinInfo__MMColParamDate & "-2000, '20000101')
      AND Datein <  DATEADD(Year, " & walkinInfo__MMColParamDate & "-2000, '20010101')"

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources