CODEWITHSUNDEEP
sqlsql-server
the_dopamine
the_dopamine

Reputation: 899

SQL Stored Procedure to get Date and Time

I wish to create a stored procedure which can retrieve the datetime less than or greater than current sys date.. in my table,startdate and enddate has the value as 'datetime'

How do I get details between startdate and enddate in SQL stored procedure?

thanks in advance

Upvotes: 1

Views: 11213

Answers (4)

Rahul Chhabria
Rahul Chhabria

Reputation: 1

There are two things:

1> To get todays date we can write
SET @today_date = GETTDDT();  

2> To get Current time we can us ethe following query:

SET @today_time = (SELECT                                            
                digits(cast(hour(current time) as decimal(2,0)))||   
                digits(cast(minute(current time) as decimal(2,0)))|| 
                digits(cast(second(current time) as decimal(2,0)))   
              FROM sysibm/sysdummy1);

Upvotes: 0

sh_kamalh
sh_kamalh

Reputation: 3901

Considering this table definition

CREATE TABLE [dbo].[Dates](
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NOT NULL
)

I assume that if you pass a date you want to know which rows satisfy the condition: startDate < date < EndDate. If this is the case you can use the query: 

select * 
from Dates 
where convert(datetime, '20/12/2010', 103) between StartDate and EndDate;

A stored procedure could look like: 

ALTER PROCEDURE [dbo].[GetDataWithinRange]
    @p_Date datetime
AS
BEGIN
    SELECT *
    from Dates 
    where @p_Date between StartDate and EndDate;
END

Upvotes: 3

SteveCav
SteveCav

Reputation: 6719

eg:

SELECT *
FROM MyTable
WHERE DATEDIFF ('d',mydatefield ,getdate() ) < 3

gets within 3 days

Upvotes: 3

Brandon Montgomery
Brandon Montgomery

Reputation: 6986

It sounds like you're trying to filter data in a table based on a date range. If this is the case (I'm having some trouble understanding your question), you'd do something like this:

select    *
from      MyTable m
where     m.Date between @DateFrom and @DateTo

Now, I'm assuming your filtering dates are put into the variables @DateFrom and @DateTo.

Upvotes: 2

Related Questions