Between or great than operator SQL

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

Between or great than operator SQL

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2014-02-10 : 08:50:18

Hi guys- I need another pair of eyes here.
> Im not getting data when I execute the below 0 Rows:


select  mydate from [dbo].mytable  
where convert(varchar, mydate,101) between '11/18/2013' and '02/08/2014'

However the below gives results...


select  mydate from [dbo].mytable  
where convert(varchar, mydate,101) between '01/01/2013' and '02/08/2014'

Any suggestions please?

Thanks!

--------------------------
Joins are what RDBMS's do for a living

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-10 : 10:51:25

ugh. DON'T convert your dates to varchar. your between is using an alpha-numeric string to figure out the answer instead of a date range.
Keep [myDate] as datetime. You can let sql server implicitly convert your date string constants to datetimes or you can explicitly cast/convert them in your between expression.

The other obvious thing is that maybe you have no data between nov 18 2013 and feb 8 2014 but you do between jan 1 2013 and feb 8 2014 ?

Be One with the Optimizer
TG

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2014-02-10 : 11:52:28

Thanks a lot! RESOLVED

--------------------------
Joins are what RDBMS's do for a living

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-11 : 00:48:22

quote:
Originally posted by xhostx

Thanks a lot! RESOLVED

--------------------------
Joins are what RDBMS's do for a living

see
http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kristen
Test

22859 Posts

Posted - 2014-02-11 : 03:42:39

quote:

Originally posted by xhostx


select  mydate from [dbo].mytable  
where convert(varchar, mydate,101) between '11/18/2013' and '02/08/2014'

Couple of suggestions if I may:

Best to always specify a size when using


convert(varchar(999), ...

otherwise sooner or later the default size that SQL uses will catch you out. ("999" is just my example

)

For dates expressed as string constants best not to use '11/18/2013' style as it is ambiguous as to which component is the Day / Month / Year. Its a pity SQL was built to automatically parse those, as it uses all sorts of "hints" to decide if it will be dd/mm or mm/dd etc. That includes the language selected by the user who is logged on ... let alone server settings (which might change, particularly if you move servers) and so on.

An 8-digit string '20131118' in "yyyymmdd" format will be treated unambiguously by SQL, otherwise best to use CONVERT(Date, '11/18/2013', xxx) with the appropriate 3rd parameter indicating the date format style used, or


SET DATEFORMAT mdy

which is then the default for the rest of that batch of code.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2014-02-11 : 10:11:59

Thank you guys a lot!!

Appreciated

--------------------------
Joins are what RDBMS's do for a living

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources