casting problem in where clause

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

SQL Server 2000 Forums

Transact-SQL (2000)

casting problem in where clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-26 : 11:01:22

Suppasit writes "Guys,
I've got a problem when i try to CAST the selected filed in WHERE cluase as describes;

Example code --->

Select right(record_date,4)+'-'+right(left(record_date,5),2)+'-'+left(record_date,2) as c_date
From [PRODUCT ORDER]
Where CAST(c_date as datetime) > '2545-02-25'

I'd like to select all fileds in [PRODUCT ORDER] table that 'record_date' > 'yyyy-mm-dd' but i keep the 'record_date' colume in VARCHAR for some reason.

My table keep the varchar colume 'record_date' like --> '24/04/2545' and i try to convert them into ISO date standard for casting, the error occur in WHERE cluase, i think cause of WHERE cannot operate with 'as c_date' but i have no idea for this case. Please help me. Thank you very much.

Regards,
Suppasit"

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-04-26 : 13:01:32

You don't need to use Cast at all, but as you have found out, you CANNOT use the column alias in the WHERE clause.


set nocount on

create table [#PRODUCT ORDER] (record_date varchar(30))

insert [#PRODUCT ORDER] values ('26/04/2002')
insert [#PRODUCT ORDER] values ('26/03/2552')

Select right(record_date,4)+'-'+
       right(left(record_date,5),2)+'-'+
       left(record_date,2) as c_date 
From [#PRODUCT ORDER] 
Where right(record_date,4)+'-'+
      right(left(record_date,5),2)+'-'+
      left(record_date,2) > '2545-02-25' 

-- this works too; uses the substring function
Select right(record_date,4) + '-' + 
       substring(record_date,4,2) + '-' + 
       left(record_date,2) as c_date 
From [#PRODUCT ORDER] 
Where right(record_date,4) + '-' + 
      substring(record_date,4,2) + '-' + 
      left(record_date,2) > '2545-02-25' 

drop table [#PRODUCT ORDER] 

-- output
c_date     
---------- 
2552-03-26

c_date     
---------- 
2552-03-26

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources