| Author |
 Topic  |
|
rvan
Starting Member
28 Posts |
 Posted - 09/12/2007 : 20:49:05
|
Hi, Everyone
Nice to be back to SQLTeam forum & in previous thanks you for all your helps.
I have one question needs help with I'm try to run a query select the data call "SaleDate" contain char(8) field. Basicly I want to retrieve data for 2 months (November,December) and i don't want date & year just falling in those months.
Below query:
Select saledate
from directtax
Where Month(convert(char(8),SaleDate) = '7'
Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near '='.
Please help it is really important to me.
thanks you
ryan,
RV |
Edited by - rvan on 09/12/2007 20:50:16
|
|
|
dinakar
Flowing Fount of Yak Knowledge
USA
2507 Posts |
Posted - 09/12/2007 : 20:54:47
|
What is the datatype of the field SaleDate?
Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
ValterBorges
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 09/12/2007 : 20:56:42
|
You are missing parentheses. Also Month() returns an integer so you should drop the single quotes.
PS: If anyone is interested in a SQL Job in Connecticut with excellent pay send me your resume to ValterBorges@msn.com |
Edited by - ValterBorges on 09/12/2007 20:59:34 |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 09/12/2007 : 21:00:14
|
is SaleDate yyyymmdd?
if so then
Where datepart(mm,SaleDate) in (11,12)
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/13/2007 : 01:18:23
|
WHERE SaleDate LIKE '20071[12]%'
WHERE SaleDate >= '20071101'
AND SaleDate <= '20071231'
WHERE SaleDate BETWEEN '20071101' AND '20071231'
E 12°55'05.25"
N 56°04'39.16" |
Edited by - SwePeso on 09/13/2007 01:34:57 |
|
|
|
rvan
Starting Member
28 Posts |
Posted - 09/13/2007 : 11:48:50
|
quote:
Originally posted by dinakar
What is the datatype of the field SaleDate?
Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Data Type: Char(8) fields start with yyyymmdd
thanks,
RV |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/13/2007 : 11:57:26
|
Did you try nr's and mine suggestions?
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
rvan
Starting Member
28 Posts |
Posted - 09/13/2007 : 12:04:47
|
quote:
Originally posted by nr
is SaleDate yyyymmdd?
if so then
Where datepart(mm,SaleDate) in (11,12)
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Hi, NR
I have test this morning still give me an error message.
Select saledate
from directtax
Where datepart(mm,SaleDate) in (11,12)
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
How do you setup conversion type?
thanks
ryan--
RV |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/13/2007 : 12:20:59
|
If nr's suggestion failed, did you try mine?
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 09/13/2007 : 12:23:37
|
rvan,
That error usually means you have something in your table that really can't be a date,
like February 30th('20070230'). Try this
select * from directtax where isdate(saledate) = 0.
There may be values that aren't yyyymmdd.
Jim
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/13/2007 : 12:39:31
|
Which is fixed in either of these three approachesWHERE SaleDate LIKE '20071[12]%'
WHERE SaleDate >= '20071101'
AND SaleDate <= '20071231'
WHERE SaleDate BETWEEN '20071101' AND '20071231'
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
rvan
Starting Member
28 Posts |
Posted - 09/13/2007 : 12:48:59
|
quote:
Originally posted by jimf
rvan,
That error usually means you have something in your table that really can't be a date,
like February 30th('20070230'). Try this
select * from directtax where isdate(saledate) = 0.
There may be values that aren't yyyymmdd.
Jim
Hello, Jim
I tested your select statement "above" that my data field show 19891000 "yyyymmdd". What if the data type is the same "char"? what is the equal = 0 mean? Would I able to select by month only?
thanks--
RV |
|
|
|
rvan
Starting Member
28 Posts |
Posted - 09/13/2007 : 12:49:03
|
quote:
Originally posted by jimf
rvan,
That error usually means you have something in your table that really can't be a date,
like February 30th('20070230'). Try this
select * from directtax where isdate(saledate) = 0.
There may be values that aren't yyyymmdd.
Jim
Hello, Jim
I tested your select statement "above" that my data field show 19891000 "yyyymmdd". What if the data type is the same "char"? what is the equal = 0 mean? Would I able to select by month only?
thanks--
RV |
|
|
|
rvan
Starting Member
28 Posts |
Posted - 09/13/2007 : 13:13:11
|
quote:
Originally posted by Peso
WHERE SaleDate LIKE '20071[12]%'
WHERE SaleDate >= '20071101'
AND SaleDate <= '20071231'
WHERE SaleDate BETWEEN '20071101' AND '20071231'
E 12°55'05.25"
N 56°04'39.16"
Hi, Peso
According to your first method would work for me. I would like to said, thanks you.
But I'm still interest others method out there.
thanks against
RV |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 09/13/2007 : 13:38:30
|
rvan,
my query was just to test the integrity of your data "ISDATE(saledate) = 0 " means that saledate can't be converted to a date. "ISDATE(saledate) = 1" means that it could be.
In your case 19891000 is October 0,1989, which
isn't a real date, it's out-of-range. But Peso's first method picks that up. You just should be aware
that there may be other problems in that field, like someone entered a date in mmddyyyy format.
Good luck!
Jim |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/13/2007 : 13:54:06
|
"But I'm still interest others method out there"
Yeah, don't store dates in a "text" column, use a DateTime datatype instead.
How many solutions do you want?
Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 09/13/2007 : 13:54:58
|
[Standard Admonishment #1]
Always store dates in the datetime datatype
[/Standard Admonishment #1]
CODO ERGO SUM |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/13/2007 : 14:06:22
|
"Yeah? Then how are we going to get any waork from all the scre ups out there?"
I plug client firewalls so they can't access SQLTeam. And prospective clients too. Let me know if you want to borrow my zombies to plug your clients & prospects too. 
|
|
|
|
rvan
Starting Member
28 Posts |
Posted - 09/13/2007 : 16:25:34
|
quote:
Originally posted by Kristen
"But I'm still interest others method out there"
Yeah, don't store dates in a "text" column, use a DateTime datatype instead.
How many solutions do you want?
Kristen
Hi, Kristen
Thanks for your reply but I'm not Sql server or DBA here and had no control of the database. Just a employee try get data of the server.
thanks--
RV |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/13/2007 : 17:43:49
|
"I'm not Sql server or DBA here and had no control of the database"
Well if your SQL/DBA/Database folk are storing dates in text columns it might be as well to see if they have a good reason, or to nudge then in the direction of storing them in a [s]datatype[s/] datatime datatype column.
Validation guaranteed
Range-query guaranteed
Date calculation - like "Add one year" - guaranteed - even adding 1 year to 29th Feb, or one month to 31st Jan, etc
And so on.
"Just a employee try get data of the server"
Then I reckon you should be happy with the first solution that works 
Kristen |
Edited by - Kristen on 09/13/2007 18:39:12 |
|
|
|
Topic |
|
Select Get Particular "Month" only
