SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Date is greater than but equal to or less
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rjhe22
Constraint Violating Yak Guru

254 Posts

Posted - 06/20/2013 :  04:37:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/20/2013 :  04:39:49  Show Profile  Visit ditch's Homepage  Reply with Quote
As_At_Date BETWEEN '2013-03-31' AND '2013-06-19 23:59:59.999'


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

Edited by - ditch on 06/20/2013 04:40:23
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/20/2013 :  04:40:23  Show Profile  Reply with Quote

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


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

Edited by - visakh16 on 06/20/2013 04:40:53
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/20/2013 :  04:49:13  Show Profile  Visit ditch's Homepage  Reply with Quote
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/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/20/2013 :  04:53:31  Show Profile  Reply with Quote
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
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/20/2013 :  04:56:14  Show Profile  Visit ditch's Homepage  Reply with Quote
Nice one.
Good point.


Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/20/2013 :  05:03:25  Show Profile  Reply with Quote
Thanks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

254 Posts

Posted - 06/20/2013 :  05:58:58  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/20/2013 :  06:01:31  Show Profile  Reply with Quote
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
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

254 Posts

Posted - 06/20/2013 :  06:03:39  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/20/2013 :  06:06:25  Show Profile  Reply with Quote
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
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

254 Posts

Posted - 06/20/2013 :  06:07:12  Show Profile  Reply with Quote
ok thanks very much will try that and see how it works
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/20/2013 :  06:09:16  Show Profile  Reply with Quote
ok..let us know how you got on!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

254 Posts

Posted - 06/20/2013 :  06:09:32  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/20/2013 :  06:15:49  Show Profile  Reply with Quote
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
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

254 Posts

Posted - 06/20/2013 :  06:18:15  Show Profile  Reply with Quote
ok thanks just got a bit confused with the dates(19000331) at the end of it.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/20/2013 :  06:22:05  Show Profile  Reply with Quote
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
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

254 Posts

Posted - 06/20/2013 :  06:23:31  Show Profile  Reply with Quote
thanks for help very much appreciated
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/20/2013 :  06:27:20  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000