How to change MM/DD/YYYY format to DD/MM/YYYY format by using SQL query.

I have a database table named EMP and I have a column named
DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
YYYY format by using the CONVERT command by SQL query.What is the
solution ?
Thank you.
Amritendu Paul
Hello,
For mm/dd/yyyy
Select CONVERT(CHAR(10),columns_name,101) from Table_name
For DD/MM/YY
Select CONVERT(CHAR(10),columns_name,103) from Table_name
Thanks
Hari
<amripaul@.gmail.com> wrote in message
news:1181557806.349855.6210@.x35g2000prf.googlegrou ps.com...
>I have a database table named EMP and I have a column named
> DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
> the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
> YYYY format by using the CONVERT command by SQL query.What is the
> solution ?
> Thank you.
> Amritendu Paul
>
|||Thanx for your help.

how to change date format in a select statement

when i use this command in a aspx file

"SELECT DISTINCT Format$([dbo.classgiven.classdate], 'mm/yyyy') AS monthyear,{.......................

'Format$' is not a recognized function name.

so how do i change date from mm/dd/yyyy to mm/yyyy

Check out the CAST and CONVERT functions in SQL BOL. They have a listing of all the possible combinations of formatting you can do for datetime values.|||

Hi~

Try this:

SELECTRIGHT(CONVERT(VARCHAR(10), Column_Name, 103), 7)AS [MM/YYYY]from Table_Name

Hope it helps.

How to change COLLATION NAME for the database?

Is there a way (besides "ALTER DATABASE COLLATE ...") to change collation name for the whole database? I tried to use the "ALTER DATABASE" command, but it didn't work. And I wouldn't like to run "ALTER COLUMN" commands for over 100 tables.Originally posted by zuhara
Is there a way (besides "ALTER DATABASE COLLATE ...") to change collation name for the whole database? I tried to use the "ALTER DATABASE" command, but it didn't work. And I wouldn't like to run "ALTER COLUMN" commands for over 100 tables.

Altering the default collation of a database does not change the collations of the columns in any existing user-defined tables. These can be changed with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:

The default collation for the database. This new default collation is applied to all columns, user-defined data types, variables, and parameters subsequently created in the database. It is also used when resolving the object identifiers specified in SQL statements against the objects defined in the database.

Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables to the new collation.

All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions to the new collation.

The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, to the new default collation.
After a collation has been assigned to any object other than a column or database, you cannot change the collation except by dropping and re-creating the object. This can be a complex operation. To change the default collation for an instance of Microsoft SQL Server 2000 you must:

Make sure you have all of the information or scripts needed to re-create your user databases and all of the objects in them.

Export all of your data using a tool such as bulk copy.

Drop all of the user databases.

Rebuild the master database specifying the new collation.

Create all of the databases and all of the objects in them.

Import all of your data.|||If your database has a reasonably conventional design you are welcome to try my script generator

http://www.dbforums.com/showthread.php?threadid=926370&highlight=collation

which will generate a t-sql script for manually tearing down and rebuilding all collatable columns in a dabase.

How to cast the value in C# resulted from max() command in SQL Server Developer

Could anyone help of how to cast the value in C# resulted from max() command. To complicate the matter, from the query result I see in the Microsoft SQL Server Management Studio, if there are records the value is number. But if there are no records, the value is NULL. How do I handle these two possible different conditions?

I have tried:

- stringtest = (string)reader["MaxOrderID"];

- inttest = Convert.ToInt32((string)reader["MaxOrderID"]);

- stringtest = Convert.ToInt32(reader["MaxOrderID"]).ToString();

all failed. And what do I do if the value is NULL. And also how do I do to cope with these two different possible conditions?

For example, I have the following code:

command.CommandText ="Select max(OrderID) as 'MaxOrderID' from [Order]";command.CommandType =CommandType.Text;

command.Connection = conn;

command.Connection.Open();

reader = command.ExecuteReader();

reader.Read();

? orderID = ?reader["MaxOrderID"];

Select MAX(ISNULL(OrderID,0) as MaxOrderID from [Order] try this statment and retest your three statments again...

- stringtest = (string)reader["MaxOrderID"];

- inttest = Convert.ToInt32((string)reader["MaxOrderID"]);

- stringtest = Convert.ToInt32(reader["MaxOrderID"]).ToString();

|||

I executedSelect MAX(ISNULL(OrderID,0)) as MaxOrderID from [Order] , but the value is still NULL, when the table has no records.

I can see that it should be 0.

|||

hi dedyandy,

dedyandy:

I executedSelect MAX(ISNULL(OrderID,0)) as MaxOrderID from [Order] , but the value is still NULL, when the table has no records.

what you are getting is correct, max will return value if there's any record in table else it wont return anything i.e. its a null. you can either use 1 as default value in case there are no records or in case you've procedure then you can check something like

if @.@.Rowcount = 0 Select 1 as 'MaxOrderID'

thanks,

satish.

|||

Thanks Satish. Yes, I will use Count function first. If there are records, I will use AVG function, else return 1.

Andy.

|||

cheers

thanks,

Satish.

How to capture the error message?

Is possible to capture the message of error generated in the execution
of a command SQL?
Thanks.DECLARE@.Error

..sql statement

SELECT @.Error = @.@.ERROR

SELECT *
FROM master..sysmessages
WHERE error = @.Error|||I thank its reply, but I want to capture the specific message of the error and not it generic message. For example:

"Cannot insert the value NULL into column 'field1', table 'DB.dbo.Tab'; column does not allow nulls. INSERT fails."

instead of :

"Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails."

DECLARE@.Error

..sql statement

SELECT @.Error = @.@.ERROR

SELECT *
FROM master..sysmessages
WHERE error = @.Error

How to Call a function using OLE DB Command Transformation

Hello

i am trying to call a function from the SQL server using Ole DB command Transformation using [dbo].[ConvertToDate] ?,?,?,?

there are no errors while executing this transformation

but this function returns a value

Now i need to capture this value how do i do that using the OLE DB command Transformation or any other transformation

Thanks

Hello Please can somebody answer this question|||

I don't believe that the OLE DB Command transform can capture the return value from a scalar function. You can work around this by calling the function inside a stored procedure, and returning the function's return as an output parameter from the procedure.

There is an article that demonstrates this technique (output parameter handling, not function wrapping) here: http://wiki.sqlis.com/default.aspx/SQLISWiki/OLEDBCommandTransformationAndIdentityColumns.html?diff=y

How to Call a function using OLE DB Command Tranformation

Hello

i am trying to call a function from the SQL server using Ole DB command Transformation using [dbo].[ConvertToDate] ?,?,?,?

there are no errors while executing this transformation

but this function returns a value

Now i need to capture this value how do i do that using the OLE DB command Transformation or any other transformation

Thanks

Hello Please can somebody answer this question|||

I don't believe that the OLE DB Command transform can capture the return value from a scalar function. You can work around this by calling the function inside a stored procedure, and returning the function's return as an output parameter from the procedure.

There is an article that demonstrates this technique (output parameter handling, not function wrapping) here: http://wiki.sqlis.com/default.aspx/SQLISWiki/OLEDBCommandTransformationAndIdentityColumns.html?diff=y

How to bulk insert a file from a computer other than the sql serve

I am executing the following bulk insert command:
BULK INSERT xSat FROM 'E:\myOutput.txt' WITH (FIELDTERMINATOR = ' ')
The sql server and file:'E:\myOutput.txt' are in different computers.
The sql server generated the following error msg:
Could not bulk insert because file 'E:\myOutput.txt' could not be opened.
Operating system error code 21(The device is not ready.).
Any suggestions?
Thank you!Is E: drive letter mapped to a network path to the share that has
myOutput.txt on it? If so, and you can see the file from a process on the
box with SQL Server on then BULK INSERT should work. I suspect you're
specifying E: as the drive letter of the remote computer - that's not going
to work.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"she" <she@.discussions.microsoft.com> wrote in message
news:6E44D770-FE7D-4EBC-B351-67273CB2C18B@.microsoft.com...
> I am executing the following bulk insert command:
> BULK INSERT xSat FROM 'E:\myOutput.txt' WITH (FIELDTERMINATOR = ' ')
> The sql server and file:'E:\myOutput.txt' are in different computers.
>
> The sql server generated the following error msg:
> Could not bulk insert because file 'E:\myOutput.txt' could not be opened.
> Operating system error code 21(The device is not ready.).
> Any suggestions?
> Thank you!
>