PHP/MS SQL - automatic formatting: varchar, datetime

[=?ISO-8859-2?Q?W=B3adys=B3aw_Bodzek?=]

Hi,

I want to change 2 default behaviors of ms-sql server within a connection.

1. All varchar (etc.) fields should be returned as UTF-8.
I know about field attribute collation and convert function,
but I want simple query like:
SELECT * FROM table;
to return all varchars as UTF-8, no matter how the fields were
defined and which collation.

In MySQL I execute: "SET NAMES 'utf-8';" after connecting to server
In PgSQL I use native function: pg_set_client_encoding()

2. Default datetime format is sth like this: "Apr 20 2008 12:01PM"
I just want all datetimes fields were returned as:
"2008-04-20 12:01:02"
I can do that with function convert() (style=20) but I want
(as in question above) a simple query like:
SELECT * FROM table;
to return date and time formatted as described above.

In MySQL and PgSQL the format "2008-04-20 12:01:02" is the default
one.

Is it possible to do that? If yes, please tell me how can I do that?
Wladyslaw

[Tom van Stiphout]

On Thu, 10 Apr 2008 06:31:10 +0200, W?adys?aw Bodzek
<wbodzek_nospam@poczta_nospam.onet.plwrote:

You have of course seen the topics on UTF-8 in Books Online.
Generally speaking I think most people would consider these things
part of the presentation layer (or perhaps business layer), not of the
database layer (the topic of this newsgroup).

-Tom.

>Hi,

I want to change 2 default behaviors of ms-sql server within a connection.

1. All varchar (etc.) fields should be returned as UTF-8.
I know about field attribute collation and convert function,
but I want simple query like:
SELECT * FROM table;
to return all varchars as UTF-8, no matter how the fields were
defined and which collation.

In MySQL I execute: "SET NAMES 'utf-8';" after connecting to server
In PgSQL I use native function: pg_set_client_encoding()

2. Default datetime format is sth like this: "Apr 20 2008 12:01PM"
I just want all datetimes fields were returned as:
"2008-04-20 12:01:02"
I can do that with function convert() (style=20) but I want
(as in question above) a simple query like:
SELECT * FROM table;
to return date and time formatted as described above.

In MySQL and PgSQL the format "2008-04-20 12:01:02" is the default
one.

Is it possible to do that? If yes, please tell me how can I do that?
Wladyslaw

[Erland Sommarskog]

W?adys?aw Bodzek (wbodzek_nospam@poczta_nospam.onet.pl) writes:

I want to change 2 default behaviors of ms-sql server within a connection.

1. All varchar (etc.) fields should be returned as UTF-8.
I know about field attribute collation and convert function,
but I want simple query like:
SELECT * FROM table;
to return all varchars as UTF-8, no matter how the fields were
defined and which collation.

In MySQL I execute: "SET NAMES 'utf-8';" after connecting to server
In PgSQL I use native function: pg_set_client_encoding()

There is no setting for this in SQL Server. This is something you need to
deal with client side.

2. Default datetime format is sth like this: "Apr 20 2008 12:01PM"
I just want all datetimes fields were returned as:
"2008-04-20 12:01:02"
I can do that with function convert() (style=20) but I want
(as in question above) a simple query like:
SELECT * FROM table;
to return date and time formatted as described above.

In MySQL and PgSQL the format "2008-04-20 12:01:02" is the default
one.

SQL Server returns datetime values as binary values, and it is up to
the client to format it. You will have to ask in a PHP forum how to do
that.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[warrenkimberlys]

Usually in this situation you will find a DBA programming...

If you want to do this, simply add the functionality into the Data
abstraction layer of your programming. Why does it make a difference
how long your SELECT queries are? Either you will add the
functionality or SQL will.

On Apr 9, 11:31*pm, W³adys³aw Bodzek
<wbodzek_nospam@poczta_nospam.onet.plwrote:

Hi,

I want to change 2 default behaviors of ms-sql server within a connection.

1. All varchar (etc.) fields should be returned as UTF-8.
* * I know about field attribute collation and convert function,
* * but I want simple query like:
* * * *SELECT * FROM table;
* * to return all varchars as UTF-8, no matter how the fields were
* * defined and which collation.

* * In MySQL I execute: "SET NAMES 'utf-8';" after connecting to server
* * In PgSQL I use native function: pg_set_client_encoding()

2. Default datetime format is sth like this: "Apr 20 2008 12:01PM"
* * I just want all datetimes fields were returned as:
* * * *"2008-04-20 12:01:02"
* * I can do that with function convert() (style=20) but I want
* * (as in question above) a simple query like:
* * * *SELECT * FROM table;
* * to return date and time formatted as described above.

* * In MySQL and PgSQL the format "2008-04-20 12:01:02" is the default
* * one.

Is it possible to do that? If yes, please tell me how can I do that?

Wladyslaw