Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
Hi,
 
As stated above subject, I would like to do a code as following:
 
select UniformGroupID, price from UniformPrice where (datename(YEAR, CONVERT (datetime,EffectiveDate, 103))='2012'
and datename(MONTH, CONVERT (datetime,EffectiveDate, 103))='October')
between (datename(YEAR, CONVERT (datetime,CloseDate, 103)='2999' and datename(MONTH, CONVERT (datetime,CloseDate, 103))='December')
 
I know I'm wrongly using the BETWEEN command but the concept is something like that so how should I set it or any other command that can be used as BETWEEN command?
 

Thanks.
 
N$P
Posted 8-Jan-13 21:03pm
Comments
Christian Graus at 9-Jan-13 3:11am
   
This is illegible, I can't even work out what you're trying to do.
singping at 9-Jan-13 3:16am
   
yes, the above code is wrong just i want to show the concept that i want to display the date which is between 2 different fields (EffectiveDate & CloseDate).
Zoltán Zörgő at 9-Jan-13 3:16am
   
Try to formulate in English what you want to query, like: Every record from UniformPrice where...
singping at 9-Jan-13 3:32am
   
I would like to get the value of UniformGroupID & price from a table named UniformPrice where the year & month of effectivedate and closedate is between 2012 to 2999 and October to December.
Maciej Los at 9-Jan-13 16:22pm
   
1) Do you want to fetch data where both dates (effectivedate and closedate) are between X/2012 and XII/2999?
or
2) Do you want to fetch data where effectivedate is X/2012 and closedate is XII/2999?
or
3) Do you want to fetch data where effectivedate is between X/2012 and XII/2012, and closedate is between X/2999 and XII/2999?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try this
 
SELECT
 UniformGroupID,
 price
FROM
UniformPrice
WHERE
CONVERT (datetime,EffectiveDate, 103) BETWEEN '1/10/2012' AND '31/12/2999'
AND
CONVERT (datetime,CloseDate, 103) BETWEEN '1/10/2012' AND '31/12/2999'
 
Hope this helps
--RDBurmon
  Permalink  
v4
Comments
singping at 9-Jan-13 3:56am
   
sorry that i just want the month & year only, how can I do it?
Zoltán Zörgő at 9-Jan-13 4:29am
   
That's a nonsense. Any date greater or equal to "October 2012" is actually greater or equal to "1. October 2012". Think about it.
RDBurmon at 9-Jan-13 4:35am
   
Updated a bit . Also Zoltan is correct. Think on it
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

dear please do some practices on sql command at least basic and with operator then ask the questions
 
^]
 

http://www.1keydata.com/sql/sqlbetween.html[^]
 
http://www.w3schools.com/sql/sql_between.asp[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

singping wants this way, try and see
SELECT
 UniformGroupID,
 price
FROM
UniformPrice
WHERE
DATEPART(yyyy,EffectiveDate) BETWEEN '2012' AND '2999'
AND
DATEPART(mm,CloseDate) BETWEEN '10' AND '12'
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 550
1 mhegazy94 460
2 Ravi Bhavnani 230
3 Kornfeld Eliyahu Peter 185
4 Shemeemsha RA 160
0 Sergey Alexandrovich Kryukov 7,295
1 OriginalGriff 6,841
2 CPallini 5,420
3 George Jonsson 3,654
4 Gihan Liyanage 2,797


Advertise | Privacy | Mobile
Web04 | 2.8.140922.1 | Last Updated 9 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100