Date range - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Date range

Author

Topic

achilles
Starting Member

3 Posts

Posted - 2006-10-02 : 11:33:19

Hi, 'am fairly very new to SQL SERVER 2000.

I have this particular problem....I need to develop a stored proc
where in a parameter checks the dates between JAN 1st and DEC 31st of a particular year. The parameter is declared for year..

I'll mention a small example below...

USE PUBS
declare @year as varchar
set @year = 1993

select * from employee
where hire_date >= '@year-01-01'
and hire_date<='@year-12-31'

This is just an example to show wat i want...
the year is prompted to the user...he/she can select any year

I get the following error message....

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Can anyone help me on this......

Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-02 : 11:41:05

select * from employee
where hire_date >= @year+'-01-01'
and hire_date<=@year+'-12-31'

Why dont you use DATETIME parameter?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-10-02 : 12:02:07

You should use "date constants" which are in the format "yyyymmdd" - you would be better to avoid the hyphens in the example you give - so I would suggest:

select * from employee
where hire_date >= @year + '0101'
and hire_date <= @year + '1231'

Note also that

and hire_date <= @year + '1231'

is going to EXCLUDE anything on 31st December with a time AFTER midnight. This is only a problem if you are storing a Time as well as a Date, but that will be the case if you are, for example, using the SQL system function GetDate() to provide the date for your hire_date.

So, again using a broken-syntax example, you might be better to change:

and hire_date <= @year + '1231'

to

and hire_date < CONVERT(varchar(20), @year+1) + '0101'

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-02 : 12:23:34

[code]
select
*
from
MyTable
where
-- GE start of this year
hire_date >= dateadd(yy,@year-1900,0) and
-- LT start of next year
hire_date < dateadd(yy,@year-1899,0)
[/code]

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-10-02 : 12:36:29

Yeah, much better. Too easy to get sucked into string handling for dates ...

achilles
Starting Member

3 Posts

Posted - 2006-10-02 : 16:49:24

Thanks alot....

I got the desired result...but 'am confused with the logic here...
can u plz explain it to me....

-- GE start of this year
hire_date >= dateadd(yy,@year-1900,0) and
-- LT start of next year
hire_date < dateadd(yy,@year-1899,0)

this part of the code....

quote:

Originally posted by Michael Valentine Jones


select
	*
from
	MyTable
where
	-- GE start of this year
	hire_date >= dateadd(yy,@year-1900,0) and
	-- LT start of next year
	hire_date <  dateadd(yy,@year-1899,0)

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-02 : 17:45:43

Basically, it adds years to the datetime zero point, which is 1900-01-01. That is why it first subtracts 1900.

You may want to read through these links:

Converting Year, Month, and Day to DateTime
The functions on this link will take input parameters of Year, Month, and Day and return a datetime.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

This has a lot of background about SQL Server datetime.

Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

CODO ERGO SUM

achilles
Starting Member

3 Posts

Posted - 2006-10-03 : 09:57:36

Thank you so much micheal......it really helped me alot....

I'll definitely go thru those links .....and try to understand as much as i can....

Fianlly...thanks alot everybody

quote:
Originally posted by Michael Valentine Jones

Basically, it adds years to the datetime zero point, which is 1900-01-01. That is why it first subtracts 1900.

You may want to read through these links:

Converting Year, Month, and Day to DateTime
The functions on this link will take input parameters of Year, Month, and Day and return a datetime.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

This has a lot of background about SQL Server datetime.

Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-03 : 10:20:25

Hereafter dont forget to Use DATETIME datatype to store DATEs

Madhivanan

Failing to plan is Planning to fail

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-10-04 : 03:17:57

if you are using datetime fields could you not use

USE PUBS
declare @year as int
set @year = 1993

select * from employee
where YEAR(hire_date) = @year

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources