SQL server stored procedure between statement

Question

I want to select from table where date with between

but problem is that in database there is not datetime column there is separated dateTime year, month and day

does it possible to create new variable inside stored procedure something like that?

checkin = new DateTime(year, month, day)

this year month and day are columns in datatable

See also http://stackoverflow.com/questions/1923876/sql-server-convert-intyear-intmonth-intday-to-datetime — Robert Harvey, Feb 15 '12 at 16:17

score 3 · Answer 1 · answered Feb 15 '12 at 16:13

3

You can convert string to DateTime using the function below:

convert(datetime, '02/15/2012', 101) -- mm/dd/yyyy

answered Feb 15 '12 at 16:13

Billy

15,516
28
70
101

If you do it this way, it will be easier to extend and more obvious what is happening in the code for future developers. So if/when you want to add hr:mi to the system, you can. – jerry Feb 15 '12 at 16:38

score 2 · Answer 2 · answered Feb 15 '12 at 16:13

You could try something like:

-- dateadd formula borrowed from
-- http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

with includeDate as (
    select column1,
           column2,
           column3,
           dateadd(month,(([year]-1900)*12)+[month]-1,[day]-1) as date
    from yourTable
)
select * 
from includeDate
where date between @startDate and @endDate

score 1 · Accepted Answer · answered Feb 15 '12 at 17:58

Of course in your stored procedure, you would populate the query with the values from your table, the code below is an example of converting the separate date fields into one datetime value.

I don't know the data type of your day, month and year fields but the following should work if you have an int value:

declare @day int
declare @month int
declare @year int
declare @fulldate smalldatetime

set @day = '1'
set @month = '9'
set @year = '2012'

SELECT @fulldate = Convert(smalldatetime, Cast(@month as varchar(2)) + '/' 
    + Cast(@day as varchar(2)) + '/' + Cast(@year as varchar(4)), 101)

select Convert(varchar(10), @fulldate, 101)

If the values are stored as a string:

declare @day varchar(2)
declare @month varchar(2)
declare @year varchar(4)
declare @fulldate smalldatetime

set @day = '1'
set @month = '9'
set @year = '2012'

SELECT @fulldate = Convert(smalldatetime, @month + '/' 
    + @day + '/' + @year, 101)

select Convert(varchar(10), @fulldate, 101)

SQL server stored procedure between statement

3 Answers3