Select CHAR Error - SQL Server Forums

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

Transact-SQL (2005)

Select CHAR Error

Author

Topic

bbowser
Starting Member

43 Posts

Posted - 2007-06-07 : 11:56:14

I'm transferring an old jsp application from a db2 over to my MS SQL Server 2005 and the following will not work.

SELECT FUND_ID, AGENCY_ID, CONTRACT_ID, PROG_YEAR, CHAR(Contract_Start,USA) AS Contract_Start, CHAR(Contract_End,USA) AS Contract_End

I'm not sure what the correct statement would be to collect this information correctly and post it as a mm/dd/yyyy
Both the contract_start column and Contract_End column are listed as nvarchar(255). The dates in the columns are entered like mm/dd/yy. I've tried to take the ,USA portion off and I get an error like "Conversion failed when trying to convert nvarchar value 'mm/dd/yy' to int" Any help on this would be greatly appreciated and Thank you in advance.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-07 : 12:08:54

The Alternative in SQL Server is the CONVERT/CAST functions. Check out Books On Line for more info.

 CONVERT(Varchar,Column) AS New column

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

phdiwakar
Starting Member

15 Posts

Posted - 2007-06-07 : 12:23:59

Two ways to proceed:

1)CHAR in sql server 2005 converts an integer ASCII code to a character, you obviously dont need that. Did you try taking off the whole char function?

2)change those two columns datatype to smalldatetime instead and then use convert if needed.

bbowser
Starting Member

43 Posts

Posted - 2007-06-07 : 12:30:45

Thanks dinakar, that works but it brings in the column as it's been entered. i.e. The dates are entered as mm/dd/yy and so that's what it brings back to the web page. I need it to post it as mm/dd/yyyy. How would I accomplish this?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-07 : 12:43:11

Generally its recommended to do that kind of formatting at the front end where it costs less to do the conversion. IF you HAVE to do it in the query you can use

...CONVERT(varchar,mydatecolumn,101) as Newcolumn ...

.
Check out books on line for CONVERT function to see other formatting options.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

phdiwakar
Starting Member

15 Posts

Posted - 2007-06-07 : 12:55:17

for that to work, his columns should be smalldatetime data type.

otherwise use the roundabout way of

select convert(varchar,convert(smalldatetime, contract_start),101)

bbowser
Starting Member

43 Posts

Posted - 2007-06-07 : 13:05:25

I don't even know you but I LOVE YOU GUYS!!! It works great!!!
You two rock!!! Thank you, Thank you, Thank you!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-08 : 10:26:03

quote:
Originally posted by bbowser

Thanks dinakar, that works but it brings in the column as it's been entered. i.e. The dates are entered as mm/dd/yy and so that's what it brings back to the web page. I need it to post it as mm/dd/yyyy. How would I accomplish this?

Well. In webpage when displaying data format it using format function

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources