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
 General SQL Server Forums
 New to SQL Server Programming
 Select Get Particular "Month" only
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

rvan
Starting Member

28 Posts

Posted - 09/12/2007 :  20:49:05  Show Profile  Reply with Quote
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  Show Profile  Visit dinakar's Homepage  Reply with Quote
What is the datatype of the field SaleDate?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 09/12/2007 :  20:56:42  Show Profile  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 09/12/2007 :  21:00:14  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/13/2007 :  01:18:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

rvan
Starting Member

28 Posts

Posted - 09/13/2007 :  11:48:50  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/13/2007 :  11:57:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Did you try nr's and mine suggestions?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

rvan
Starting Member

28 Posts

Posted - 09/13/2007 :  12:04:47  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/13/2007 :  12:20:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If nr's suggestion failed, did you try mine?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 09/13/2007 :  12:23:37  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/13/2007 :  12:39:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Which is fixed in either of these three approaches
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"
Go to Top of Page

rvan
Starting Member

28 Posts

Posted - 09/13/2007 :  12:48:59  Show Profile  Reply with Quote
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
Go to Top of Page

rvan
Starting Member

28 Posts

Posted - 09/13/2007 :  12:49:03  Show Profile  Reply with Quote
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
Go to Top of Page

rvan
Starting Member

28 Posts

Posted - 09/13/2007 :  13:13:11  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 09/13/2007 :  13:38:30  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/13/2007 :  13:54:06  Show Profile  Reply with Quote
"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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/13/2007 :  13:54:58  Show Profile  Reply with Quote
[Standard Admonishment #1]
Always store dates in the datetime datatype
[/Standard Admonishment #1]





CODO ERGO SUM
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/13/2007 :  14:01:24  Show Profile  Reply with Quote
quote:
Originally posted by Michael Valentine Jones

[Standard Admonishment #1]
Always store dates in the datetime datatype
[/Standard Admonishment #1]


Yeah? Then how are we going to get any waork from all the scre ups out there?


CODO ERGO SUM



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/13/2007 :  14:06:22  Show Profile  Reply with Quote
"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.
Go to Top of Page

rvan
Starting Member

28 Posts

Posted - 09/13/2007 :  16:25:34  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/13/2007 :  17:43:49  Show Profile  Reply with Quote
"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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.34 seconds. Powered By: Snitz Forums 2000