Date is greater than but equal to or less

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

SQL Server 2012 Forums

Transact-SQL (2012)

Date is greater than but equal to or less

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-20 : 04:37:04

hi
i need to do the following but dont now how an new enough to t sql.
As_At_Date is greater than 31 March YY but equal to or less than 19 June YY
anyone an idea

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-20 : 04:39:49

As_At_Date BETWEEN '2013-03-31' AND '2013-06-19 23:59:59.999'

Duane.
http://ditchiecubeblog.wordpress.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 04:40:23

[code]
WHERE As_At_Date > DATEADD(yy,DATEDIFF(yy,0,GETDATE()),'19000331')
AND As_At_Date < DATEADD(yy,DATEDIFF(yy,0,GETDATE()),'19000620')
[/code]

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

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-20 : 04:49:13

Ahhh - OK query independent of year. Great solution Visakh

You could also do

WHERE MONTH(As_At_Date) * 100 + DAY(As_At_Date) BETWEEN 331 and 619

Duane.
http://ditchiecubeblog.wordpress.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 04:53:31

quote:
Originally posted by ditch

Ahhh - OK query independent of year. Great solution Visakh

You could also do

WHERE MONTH(As_At_Date) * 100 + DAY(As_At_Date) BETWEEN 331 and 619

Duane.
http://ditchiecubeblog.wordpress.com/

you could
However if there's an index on As_At_Date the above solution cannot take advantage of it as use of function (MONTH) over column makes it non sargable. My suggestion would still use index as I've used column without any functions applied.

also see

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

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

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-20 : 04:56:14

Nice one.
Good point.

Duane.
http://ditchiecubeblog.wordpress.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 05:03:25

Thanks

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

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-20 : 05:58:58

thanks for that.

if i wanted to take the year out of a date. so lets my my date was 20120630 and i just wanted to get the 2012 part is that a trim left on the date field

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 06:01:31

quote:
Originally posted by rjhe22

thanks for that.

if i wanted to take the year out of a date. so lets my my date was 20120630 and i just wanted to get the 2012 part is that a trim left on the date field

you could just use YEAR(datefield) for that unless you're using it in WHERE clause as a filter

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

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-20 : 06:03:39

i might be what i have to do it take the year from Date To field make that a variable and then see if the As At Date is > 31 March + YEAR(Date To) and < 19 June + YEAR(Date To)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 06:06:25

quote:
Originally posted by rjhe22

i might be what i have to do it take the year from Date To field make that a variable and then see if the As At Date is > 31 March + YEAR(Date To) and < 19 June + YEAR(Date To)

you could simply do this


...
WHERE As_At_Date > DATEADD(yy,DATEDIFF(yy,0,[Date To]),'19000331')
AND As_At_Date < DATEADD(yy,DATEDIFF(yy,0,[Date To]),'19000620')

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

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-20 : 06:07:12

ok thanks very much will try that and see how it works

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 06:09:16

ok..let us know how you got on!

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

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-20 : 06:09:32

would that work if i had to look up different dates in different years like some might be 2012 and some 2013 and going forward 2014 etc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 06:15:49

yep...it will work based on whatever date you've on DateTo field. It checks based on year value of DateTo after appending the 31 Mar and 19 Jun part

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

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-20 : 06:18:15

ok thanks just got a bit confused with the dates(19000331) at the end of it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 06:22:05

quote:
Originally posted by rjhe22

ok thanks just got a bit confused with the dates(19000331) at the end of it.

thats the base date for sql server with your required month day part appended (31 Mar). Similarly for end date

see here to understand logic used

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

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

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-20 : 06:23:31

thanks for help very much appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 06:27:20

you're welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources