CODEWITHSUNDEEP
sql-serverdatetime
Prem
Prem

Reputation: 5987

How to pass the Datetime value as parameter to a stored procedure?

In the table, I have a column called as BillDate (Datetime). The datetime is stored in the format YYYY-MM-DD (Example: 2012-01-01 00:00:00.000). Now I want to retrieve the values between the range of datetime values. When I used query as the following I could not get the result:

SELECT * FROM RequestHeader
WHERE 
  CONVERT(VARCHAR, RH.BillDate ,105) BETWEEN CONVERT(VARCHAR, @FromDate,105) 
  AND CONVERT(VARCHAR,@ToDate , 105)

@FromDate is passed as : '2012-01-01'
@ToDate is passed as : '2012-01-01'

Upvotes: 4

Views: 119841

Answers (8)

hari
hari

Reputation: 1

i have procedure: i can call it like this,

 create procedure insert_log (
       @id int,
       @date datetime2,
       @in_time time,
       @out_time time)
       as
       begin
       insert into student_log values(@id,@date,@in_time,@out_time)
       end;


     declare @date datetime2=getdate(),
            @in_time time=convert(time,getdate()),
            @out_time time=dateadd(minute,90,convert(time,getdate()))
    
    exec insert_log
        @id=3,
        @date=@date,
        @in_time=@in_time,
        @out_time=@out_time;

Upvotes: 0

David Moorhouse
David Moorhouse

Reputation: 1665

The title and the question address different concepts. The title mentions "stored procedure" and the question and answers are direct selects from a table.

Upvotes: 0

user2432460
user2432460

Reputation: 1

SELECT * FROM RequestHeader
WHERE BillDate 
BETWEEN convert(Datetime,@FromDate,102) 
AND convert(Datetime,@ToDate,102)

Upvotes: 0

Las Ten
Las Ten

Reputation: 1175

Keep it as simple as possible

SELECT * FROM RequestHeader
WHERE 
  BillDate BETWEEN @FromDate AND @ToDate

@FromDate and @ToDate are, of course, datetime variables, not varchars.

Upvotes: 0

Tim
Tim

Reputation: 11

In your example @FromDate is the same as @ToDate. There are no values between two equivalent value. I'm going to assume you have those values as just an example so I'll try to answer anyway.

First I don't see the need to convert the BillDate to VARCHAR. If the parameters passed are also dates there is no reason to convert those either. If not it can be converteed.

I created a simple test to confirm my assumptions:

DECLARE @FromDate VARCHAR(50)
DECLARE @ToDate VARCHAR(50)

SET @FromDate = '2012-01-01'
SET @ToDate = '2012-01-05'


INSERT INTO RequestHeader (BillDate) VALUES('12/1/2011')
INSERT INTO RequestHeader (BillDate) VALUES('1/3/2012')
INSERT INTO RequestHeader (BillDate) VALUES('1/4/2012')
INSERT INTO RequestHeader (BillDate) VALUES('1/5/2012')

SELECT * FROM RequestHeader
WHERE
    BillDate BETWEEN CAST(@FromDate AS DateTime) AND CAST(@ToDate AS DateTime)

Results:

BillDate
2012-01-03 00:00:00.000
2012-01-04 00:00:00.000
2012-01-05 00:00:00.000

(3 row(s) affected)

Upvotes: 1

Oleg Dok
Oleg Dok

Reputation: 21766

You need to use 121-format code intead of 105, like this:

 SELECT * FROM RequestHeader
WHERE 
  CONVERT(VARCHAR, RH.BillDate ,121) BETWEEN CONVERT(VARCHAR, @FromDate,121) 
  AND CONVERT(VARCHAR,@ToDate , 121)

BUT if it stored as DATETIME type, you need not to use CONVERT, and optimizer can use indexes in this case:

SELECT * FROM RequestHeader
WHERE 
  RH.BillDate BETWEEN @FromDate AND @ToDate

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726569

The best way to address the issue is to pass datetime values as datetime values, not as strings. This would be easier both on the SQL server and on the readers of your stored procedure's source code.

Upvotes: 1

Nonym
Nonym

Reputation: 6299

Try this:

SELECT * FROM RequestHeader
WHERE 
  CONVERT(VARCHAR, RH.BillDate ,105) BETWEEN CONVERT(VARCHAR, CAST(@FromDate AS DATETIME),105) 
  AND CONVERT(VARCHAR, CAST(@ToDate AS DATETIME), 105)

Unfortunately, using this method doesn't utilize indexes, so if you're hoping that you'll have fast query execution in the event that your date columns are indexed, you won't feel it here.

Upvotes: 3

Related Questions