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.
| Author |
Topic |
|
waterduck
Aged Yak Warrior
982 Posts |
 Posted - 2009-06-15 : 05:59:30
|
| [code]SELECT m.st_code, m.st_desc, th.total_rm*th.forex_rate as total_rm, th.total_cn*th.forex_rate as total_cn, th.qtt_out - th.quantity as qty, th.dateFROM st_mast m inner join (SELECT st_code, t.trx_type, t.qtt_out, t.quantity, t.total_price, h.date, CASE WHEN h.forex_rate = 0 then 1 else h.forex_rate END as forex_rate, CASE WHEN t.trx_type IN('CN','CNC','GRO') then 0 else t.total_price END as total_rm, CASE WHEN t.trx_type IN('CN','CNC','GRO') then t.total_price else 0 END as total_cn FROM st_trx t right outer join ( SELECT CASE WHEN trx_type in('DO','CDO','DOL') then do_no WHEN trx_type in('INV','CS','CN','DN','POS','INC','CNC') then in_no END as ref_no, CASE WHEN trx_type in('DO','CDO','DOL') then (CASE WHEN in_date IS NULL or in_date='' then do_date else in_date END) WHEN trx_type in('INV','CS','CN','DN','POS','INC','CNC') then in_date END as date, forex_rate, trx_type FROM st_head)h on t.trnx_ref = h.ref_no and t.trx_type=h.trx_type WHERE date>='2007-05-01 00:00:00.000' and date<='2008-06-31 00:00:00.000')th on m.st_code=th.st_codeWHERE m.st_code>='111' and m.st_code<='123'ORDER BY m.st_code[/code]Error message[code]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.[/code]I never convert to char or varchar...how come convertion occur??w |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-15 : 06:07:02
|
quote:
Originally posted by waterduck
WHERE date>='2007-05-01 00:00:00.000' and date<='2008-06-31 00:00:00.000')th
How many days are there in June, you say?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-15 : 06:08:26
|
As suggested in your other topic, always use an open-ended criteria when searching for dates.WHERE date>='2007-05-01 00:00:00.000' and date<='2008-06-31 00:00:00.000')thWHERE date >= '20070501' and date <'20080701') AS th
E 12°55'05.63"N 56°04'39.26" |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-06-15 : 06:15:31
|
quote:
Originally posted by PesoHow many days are there in June, you say?
you must have had a clown for breakfast 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-15 : 06:27:34
|
quote:
Originally posted by webfred
quote:
Originally posted by PesoHow many days are there in June, you say?
you must have had a clown for breakfast
No, you're never too old to Yak'n'Roll if you're too young to die.
What he does in private is entirely his own business. It's not up to us to judge. I understand that some people find clowns attractive.......Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-06-15 : 21:39:10
|
| woops i was rushing back home, din notice tat...hehe |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-16 : 00:47:08
|
Well, don't rush then and expect others to fix your problem queries.It's your assignment and your job. If you want to become a better database programmer (as hinted in your other topics), you have to learn how to troubleshoot your queries.This can't be done with rushing.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-06-16 : 01:33:17
|
| >"< thx peso, i reli like this forum...giv me so much knowledge about how sql can be play |
|
|
|
|
|
|
|
|
Convertion that never happen?