?
Solved

How to replace DateSerial in SQL Server Stored Procedure ?

Posted on 2005-04-11
4
Medium Priority
?
678 Views
Last Modified: 2008-02-01
The following statement works fine in my Access application but when I moved it into SQL Server as a stored procedure,
I get the error message:

Error 195: DateSerial is not a recognized function name:

((tblCustomers.DateLost)<=DateSerial(Year(#" & Me.dtFullDate & "#)-[tblStatesAll].[BondsFS],6,30))

Could you tell me what function I could use to replace DateSerial in the context I am using it above. I would really appreciate it if you could show me the implementation of the replacement function in my SQL statement above.
 
The value returned by tblStatesAll.BondsFS is an integer value between 1 and 5. For example if BondsFS is 4 and
Me.dtFullDate is 1/1/2004. The DateSerial(Year(#1/1/2004#)-4,6,30) is 6/30/2004. Thus, the result is
DateLost <= 6/30/2000 in my example.

My question is how would I write this in SQL Server stored procedure since I cannot use DateSerial.
0
Comment
Question by:zimmer9
  • 2
4 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 13756018
0
 
LVL 11

Accepted Solution

by:
Jokra_the_Barbarian earned 2000 total points
ID: 13756523
If you want to return as string:
'6/'+'30/'+convert(varchar(4),datepart(yy,@dtFullDate)-@iBondsFS)

If you want to return as date:
convert(datetime,'6/'+'30/'+convert(varchar(4),datepart(yy,@dtFullDate)-@iBondsFS),101)

So...

Create Procedure dbo.myProc (@dtFullDate datetime, @iBondsFS int) AS

SELECT ....
FROM   ....
WHERE (tblCustomers.DateLost < convert(datetime,'6/'+'30/'+convert(varchar(4),datepart(yy,@dtFullDate)-@iBondsFS),101))
     OR (tblCustomers.DateLost = convert(datetime,'6/'+'30/'+convert(varchar(4),datepart(yy,@dtFullDate)-@iBondsFS),101))
0
 

Author Comment

by:zimmer9
ID: 13756854
Could you explain the last value ,101 ?
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question