PHP/MS SQL - automatic formatting: varchar, datetime

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

Re: PHP/MS SQL - automatic formatting: varchar, datetime

On Thu, 10 Apr 2008 06:31:10 +0200, W?adys?aw Bodzek
wrote:

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

Re: PHP/MS SQL - automatic formatting: varchar, datetime

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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: PHP/MS SQL - automatic formatting: varchar, datetime

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=A0pm, W=B3adys=B3aw Bodzek
wrote:
> 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.
> =A0 =A0 I know about field attribute collation and convert function,
> =A0 =A0 but I want simple query like:
> =A0 =A0 =A0 =A0SELECT * FROM table;
> =A0 =A0 to return all varchars as UTF-8, no matter how the fields were
> =A0 =A0 defined and which collation.
>
> =A0 =A0 In MySQL I execute: "SET NAMES 'utf-8';" after connecting to serve=
r
> =A0 =A0 In PgSQL I use native function: pg_set_client_encoding()
>
> 2. Default datetime format is sth like this: "Apr 20 2008 12:01PM"
> =A0 =A0 I just want all datetimes fields were returned as:
> =A0 =A0 =A0 =A0"2008-04-20 12:01:02"
> =A0 =A0 I can do that with function convert() (style=3D20) but I want
> =A0 =A0 (as in question above) a simple query like:
> =A0 =A0 =A0 =A0SELECT * FROM table;
> =A0 =A0 to return date and time formatted as described above.
>
> =A0 =A0 In MySQL and PgSQL the format "2008-04-20 12:01:02" is the default=

> =A0 =A0 one.
>
> Is it possible to do that? If yes, please tell me how can I do that?
>
> Wladyslaw