Dates on the Where Statement

Question

Post reply

Dates on the Where Statement

cintijohn

SSC Enthusiast

Points: 116
More actions
April 13, 2010 at 7:20 am

#213791

Before I start I did look around the forums and unfortunately couldn't find anything that would point me in the right direction or I missed the obvious.
I need to look at a personnel table of those hired on or after 4/15/2008...
select * from personnel
where hiredate >= 20080415
hiredate(datetime, null)
The above syntax generated the error message "Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime."
What am I doing wrong???
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

select * from personnel

where hiredate >= '20080415'

enclose the date in apostrophes '

😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

cintijohn SSC Enthusiast Points: 116 More actions · Answer 2

cintijohn

SSC Enthusiast

Points: 116

April 13, 2010 at 11:56 am

#1150941

It worked

Thanks!

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

Also, keep in mind that if you don't include the hours & minutes, SQL assumes 00:00:00. That can be important sometimes. If you were searching for <= instead of >= you might want to code:

where hiredate <= '2008-04-15 23:59:59:999'

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 4

homebrew01 (4/13/2010)
Also, keep in mind that if you don't include the hours & minutes, SQL assumes 00:00:00. That can be important sometimes. If you were searching for <= instead of >= you might want to code:
where hiredate <= '2008-04-15 23:59:59:999'

Carefull.... '2008-04-15 23:59:59:999' automatically rounds UP to 2008-04-16. It's far better to say (in this case) ...

WHERE HireDate < '2008-04-16'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)