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 22: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 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  
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.techonthenet.com/sql/between.php[^]
 

http://www.1keydata.com/sql/sqlbetween.html[^]
 
http://www.w3schools.com/sql/sql_between.asp[^]
  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 334
1 Kornfeld Eliyahu Peter 325
2 Maciej Los 239
3 OriginalGriff 188
4 King Fisher 185
0 OriginalGriff 6,303
1 DamithSL 4,764
2 Maciej Los 4,306
3 Kornfeld Eliyahu Peter 3,914
4 Sergey Alexandrovich Kryukov 3,538


Advertise | Privacy | Mobile
Web02 | 2.8.141220.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