Author |
Topic |
rvan
Starting Member
28 Posts |
Posted - 2007-09-12 : 20:49:05
|
Hi, EveryoneNice 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 youryan,RV |
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-12 : 20:54:47
|
What is the datatype of the field SaleDate?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2007-09-12 : 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 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-12 : 21:00:14
|
is SaleDate yyyymmdd?if so thenWhere 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
30421 Posts |
Posted - 2007-09-13 : 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" |
|
|
rvan
Starting Member
28 Posts |
Posted - 2007-09-13 : 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 yyyymmddthanks,RV |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-13 : 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 - 2007-09-13 : 12:04:47
|
quote: Originally posted by nr is SaleDate yyyymmdd?if so thenWhere 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, NRI 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 1The 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?thanksryan--RV |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-13 : 12:20:59
|
If nr's suggestion failed, did you try mine? E 12°55'05.25"N 56°04'39.16" |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-09-13 : 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 thisselect * from directtax where isdate(saledate) = 0.There may be values that aren't yyyymmdd.Jim |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-13 : 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 - 2007-09-13 : 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 thisselect * from directtax where isdate(saledate) = 0.There may be values that aren't yyyymmdd.Jim
Hello, JimI 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 - 2007-09-13 : 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 thisselect * from directtax where isdate(saledate) = 0.There may be values that aren't yyyymmdd.Jim
Hello, JimI 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 - 2007-09-13 : 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, PesoAccording 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 againstRV |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-09-13 : 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, whichisn't a real date, it's out-of-range. But Peso's first method picks that up. You just should be awarethat there may be other problems in that field, like someone entered a date in mmddyyyy format.Good luck!Jim |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-13 : 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)
7020 Posts |
Posted - 2007-09-13 : 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
22859 Posts |
Posted - 2007-09-13 : 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 - 2007-09-13 : 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, KristenThanks 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
22859 Posts |
Posted - 2007-09-13 : 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 guaranteedRange-query guaranteedDate calculation - like "Add one year" - guaranteed - even adding 1 year to 29th Feb, or one month to 31st Jan, etcAnd 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 |
|
|
Previous Page&nsp;
Next Page
|