473,721 Members | 1,832 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_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
Apr 10 '08 #1
3 6692
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
Apr 10 '08 #2
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, es****@sommarsk og.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
Apr 10 '08 #3
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
Apr 12 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
9442
by: Gerrit Holl | last post by:
Posted with permission from the author. I have some comments on this PEP, see the (coming) followup to this message. PEP: 321 Title: Date/Time Parsing and Formatting Version: $Revision: 1.3 $ Last-Modified: $Date: 2003/10/28 19:48:44 $ Author: A.M. Kuchling <amk@amk.ca> Status: Draft Type: Standards Track
3
146674
by: David Sharp | last post by:
I'm trying to find a way to format a FLOAT variable into a varchar in SQL Server 2000 but using CAST/CONVERT I can only get scientific notation i.e. 1e+006 instead of 1000000 which isn't really what I wanted. Preferably the varchar would display the number to 2 decimal places but I'd settle for integers only as this conversion isn't business critical and is a nice to have for background information. Casting to MONEY or NUMERIC before...
1
7499
by: shearichard | last post by:
Hi - I have written some python to insert a row into a table using MySQLDB. I have never before written SQL/Python using embedded parameters in the SQL and I'm having some difficulties. Could someone point me in the right direction please ? The python looks like this : import MySQLdb import MySQLdb.cursors conn = MySQLdb.Connect(host='localhost', user='abc,passwd='def',
4
8330
by: Ken Wigle | last post by:
All, I would be very grateful for any help on this question. I have an application in asp.net 2.0 where I dynamically create a datatable and then bind that to a gridview. Unfortunately, the date column always shows the date and time while I just want the short date. I have tried applying a format string {0:d} but to no avail. I saw a lot of posts regarding the htmlencode property but I do not know how to turn that off for a...
3
3474
by: SparkByte | last post by:
Now I know this has been asked before, but I am not getting the same error as mentioned in the earlier posts I could find. Server is SQL 2000 Ent. As you can see the table is for parsing Security event logs. This information is then used to create reports that are sent out to clients. Problem: The Strings and message values from the EventLogs gets truncated when pulled from the servers. (68 of them) When I try to set the columns to...
2
3331
by: bryars | last post by:
I want to write some SQL which results in an automatic conversion of a datetime to a string in a format suitable for the Language of the connection (either by explicitly setting the Language in the connection string, or by setting the default language in for the user used for the connection.) The casting from string to datetime uses the language setting: Data Source=localhost\sqlexpress;Initial Catalog=master;Persist Security...
4
1395
by: =?Utf-8?B?QW5kcmV3?= | last post by:
hi, my sp takes datetime in 'yyyyMMdd HH:mm:ss' format. however my c# datetimepicker takes 'dd/MM/yy HH:mm:ss' format. how do i convert/parse the datetime value from the datetimepicker to the sp datetime format ? I've tried: eg. IFormatProvider culture = new CultureInfo("en-US", true);
3
30832
by: Tim | last post by:
Folks, I'm trying to format a print string so that it reports progress whilst processing a looping structure with a date time stamp appended to the end of the string. This started out life as a simple need to create a display that show progress to users when updating large tables in a data warehouse in real time. I have subsequently address that need through a different method, however I am challenged by the seeming impossibility to...
1
8325
Manikgisl
by: Manikgisl | last post by:
But the problem is we have dates in Varchar instead Datetime While Converting Varchar To Datetime All four formats are unable to Convert ie select Convert(Datetime,'18-11-2008 2:35:19 PM',102) -- error
0
8844
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9370
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9218
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9132
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9067
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8009
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6672
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
3191
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2578
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.