Re: [Hsqldb-user] Convert function give error

[nitingautam <gau...@gm...>]

TO_CHAR is not supported by MSSQL Server...
I have to write a query that in both MSSQL and HSQLDB.

I tried
(convert(varchar,month(init_cret_ts))+'/'+convert(varchar,day(init_cret_ts))+'/'+convert(varchar,year(init_cret_ts)))
as INIT_CRET_TS

it works in MSSQL but not in HSQLDB . It says Wrong datatype: MONTH


fredt wrote:
> 
> You can use the TO_CHAR function for custom date styles.
> 
> The CONVERT function you are using is specific to the server you mention. 
> The standard CONVERT function does not take the style argument. We do 
> support this form of CONVERT.
> 
> Fred
> ----- Original Message ----- 
> From: "nitingautam" <gau...@gm...>
> To: <hsq...@li...>
> Sent: 25 June 2009 17:48
> Subject: Re: [Hsqldb-user] Convert function give error
> 
> 
> 
> Thanks for a quick reply fredt =)
> 
> If you see in CONVERT am using style 101 for a date format. How I use this
> with CAST.
> Don't HSQLDB support CONVERT??
> 
> 
> 
> 
> fredt wrote:
>>
>> Use CAST(init_cres_ts AS VARCHAR(50)), or you can omit the (50)
>>
>> Fred
>> ----- Original Message ----- 
>> From: "nitingautam" <gau...@gm...>
>> To: <hsq...@li...>
>> Sent: 25 June 2009 15:08
>> Subject: [Hsqldb-user] Convert function give error
>>
>>
>>
>> Hi All,
>>
>> This is my first post in this forum.
>> I am using HSQLDB as in memory DB to junit my DAO classes.
>> DAO classes has SQL Syntax for SQLServer Database.
>>
>> In one of query I am getting following error while execution
>>
>> QUERY
>> SELECT TOP 1 ORDR_ID,CONVERT(varchar(50), init_cret_ts, 101) as
>> INIT_CRET_TS
>> FROM ORDR WHERE ORDR_ID  = '21'"
>>
>> ERROR
>> java.sql.SQLException: Unexpected token: VARCHAR in statement [SELECT TOP
>> 1
>> ORDR_ID,CONVERT(varchar(]
>> at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
>>
>>
>> If I remove (50) from the above query and use only VARCHAR then i get
>> following error
>>
>> ERROR
>> java.sql.SQLException: Wrong data type: INIT_CRET_TS in statement [SELECT
>> TOP 1 ORDR_ID,CONVERT(varchar, init_cret_ts]
>>
>>
>>
>> HSQLDB Gurus please help me in this.
>>
>> Regards,
>> -- 
>> View this message in context:
>> http://www.nabble.com/Convert-function-give-error-tp24203936p24203936.html
>> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>>
>>
>> ------------------------------------------------------------------------------
>> _______________________________________________
>> Hsqldb-user mailing list
>> Hsq...@li...
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>
>>
>> ------------------------------------------------------------------------------
>> _______________________________________________
>> Hsqldb-user mailing list
>> Hsq...@li...
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>
>>
> 
> -- 
> View this message in context: 
> http://www.nabble.com/Convert-function-give-error-tp24203936p24206686.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
> 
> 
> ------------------------------------------------------------------------------
> _______________________________________________
> Hsqldb-user mailing list
> Hsq...@li...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user 
> 
> 
> ------------------------------------------------------------------------------
> _______________________________________________
> Hsqldb-user mailing list
> Hsq...@li...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> 
> 

-- 
View this message in context: http://www.nabble.com/Convert-function-give-error-tp24203936p24266672.html
Sent from the HSQLDB - User mailing list archive at Nabble.com.