Data Conversion

This should be ez but I can't get it working;
Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
to 'dd/mm/yyyy' and 'dd/mm/yy' format?
Db on SQL 2005.
Try
set dateformat mdy
select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
Linchi
"TBoon" wrote:

> This should be ez but I can't get it working;
> Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
> to 'dd/mm/yyyy' and 'dd/mm/yy' format?
> Db on SQL 2005.
|||testing
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E4C66EE1-B961-41BC-B555-818C2EA41599@.microsoft.com...[vbcol=seagreen]
> Try
> set dateformat mdy
> select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
> select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
> Linchi
> "TBoon" wrote:

Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty existing table!

Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty
existing table!
That is, A is the existing table and it is emtpy, I want to add one NOT
NULL COLUMN (col_new) to A using following T-SQL statement, then it
will fail.

ALTER TABLE A ADD
col_new varchar(600) NOT NULL
GO

You should change it to these statements in SQLServer2000:

ALTER TABLE A ADD
col_new varchar(600) NULL
ALTER TABLE A ALTER COLUMN col_new varchar(600) NOT NULL
GO

ah, ridiculous! right?

Fortunately, this stupid behavior is changed in SQLServer2005. The
first T-SQL statements works.Hi,

You can use a workaround in this case... Put a DEFAULT constraint on
your column and it will work...

Enjoy,

Cdric Del Nibbio
MCSD .NET
MCTS SQL Server 2005
http://cedric-delnibbio-sql.blogspot.com
aling a crit :

Quote:

Originally Posted by

Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty
existing table!
That is, A is the existing table and it is emtpy, I want to add one NOT
NULL COLUMN (col_new) to A using following T-SQL statement, then it
will fail.
>
ALTER TABLE A ADD
col_new varchar(600) NOT NULL
GO
>
You should change it to these statements in SQLServer2000:
>
ALTER TABLE A ADD
col_new varchar(600) NULL
ALTER TABLE A ALTER COLUMN col_new varchar(600) NOT NULL
GO
>
ah, ridiculous! right?
>
Fortunately, this stupid behavior is changed in SQLServer2005. The
first T-SQL statements works.