Select Get Particular "Month" only

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Get Particular "Month" only

Author

Topic

rvan
Starting Member

28 Posts

Posted - 2007-09-12 : 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

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 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

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 yyyymmdd

thanks,

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 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

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 this
select * 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 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"

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 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 - 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 this
select * from directtax where isdate(saledate) = 0.
There may be values that aren't yyyymmdd.

Jim

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, 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
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, 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

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

Posted - 2007-09-13 : 14:01:24

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

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, 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

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 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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources