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_e ncoding()

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]

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

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_e ncoding()
>
>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]

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_e ncoding()

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[warrenkimberlys@gmail.com]

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 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_e ncoding()
>
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