473,320 Members | 1,948 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Format a datetime columns output?

CK
Hi All,
Currently the query returns 2006-03-27 00:00:00, can I make it output
03/27/2006, I want to truncate the time, and replace the hyphens with
forward slashes. Any ideas?

Thanks In Advance,
~CK
Jun 7 '06 #1
6 7144
CK wrote:
Hi All,
Currently the query returns 2006-03-27 00:00:00, can I make it output
03/27/2006, I want to truncate the time, and replace the hyphens with
forward slashes. Any ideas?

Thanks In Advance,
~CK


SQL Server has no control over how dates are displayed. You need to fix
the formatting in your client application or development environment.
It's hard to help you because you haven't told us what your client
environment is.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Jun 7 '06 #2
CK (c_**********@hotmail.com) writes:
Currently the query returns 2006-03-27 00:00:00, can I make it output
03/27/2006, I want to truncate the time, and replace the hyphens with
forward slashes. Any ideas?


To add to David's reply, if you are using Query Analyzer for output,
you can change how dates are formatted under Tools->Options->Connections.
Check "Use Regional Settings...".

You can also do it in an SQL query, look up CASE and CONVERT in Books
Online. But this is a really poor alternative. Date formatting should
be handled by the client, so that the user's regional settings can be
respected.
--
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
Jun 7 '06 #3
CK

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
CK (c_**********@hotmail.com) writes:
Currently the query returns 2006-03-27 00:00:00, can I make it output
03/27/2006, I want to truncate the time, and replace the hyphens with
forward slashes. Any ideas?


To add to David's reply, if you are using Query Analyzer for output,
you can change how dates are formatted under Tools->Options->Connections.
Check "Use Regional Settings...".

You can also do it in an SQL query, look up CASE and CONVERT in Books
Online. But this is a really poor alternative. Date formatting should
be handled by the client, so that the user's regional settings can be
respected.


I used , Convert(varchar(25), L.FromDate, 101) AS FromDate in the View and
then CAST(FromDate AS DateTime)>= '03/27/2004' in the query. This is an
internal application but I see why it should be done in code. Thanks!
Jun 7 '06 #4
Erland Sommarskog wrote:
CK (c_**********@hotmail.com) writes:
Currently the query returns 2006-03-27 00:00:00, can I make it output
03/27/2006, I want to truncate the time, and replace the hyphens with
forward slashes. Any ideas?


To add to David's reply, if you are using Query Analyzer for output,
you can change how dates are formatted under Tools->Options->Connections.
Check "Use Regional Settings...".

You can also do it in an SQL query, look up CASE and CONVERT in Books
Online. But this is a really poor alternative. Date formatting should
be handled by the client, so that the user's regional settings can be
respected.


You can't actually format dates in a query at all. You can output a
string that looks like a date. I know you know this Erland, but the
difference is worth stating given that the application probably treats
dates and strings very differently.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Jun 8 '06 #5
David Portas (RE****************************@acm.org) writes:
You can't actually format dates in a query at all. You can output a
string that looks like a date. I know you know this Erland, but the
difference is worth stating given that the application probably treats
dates and strings very differently.


Well, it depends on what the application will use the output for. If it's
going into a report, it's just another string for the application. If the
application is going do some date logic with it, the result can only be
confusion.

--
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
Jun 8 '06 #6
CK (c_**********@hotmail.com) writes:
I used , Convert(varchar(25), L.FromDate, 101) AS FromDate in the View
and then CAST(FromDate AS DateTime)>= '03/27/2004' in the query. This
is an internal application but I see why it should be done in code.


This looks just wrong to me.

If you have a view, and need a condition on it with >=, why one Earth would
you could convert the date to a string? And why would you convert it to a
string that only works sometimes? When working with dates in literals in
SQL Server you should use the format YYYYMMDD, because this format is always
interpreted the same. Most other format depends on language and date format
settings.

If you are passing dates from the application as parameters, you should use
parameterised statements and pass datetime values as such not as strings.
--
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
Jun 8 '06 #7

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

Similar topics

2
by: Bernd Lambertz | last post by:
I have a problem with bcp and format files. We changed our databases from varchar to nvarchar to support unicode. No problems so fare with that. It is working fine. But now I need a format...
2
by: Tatiana Zadiraka | last post by:
I use DB2 8.1 with FixPack 5. In command line for sql I get all DATE columns only in MM-DD-YYYY format. Of course, DATE('20-12-2003') gives me an error SQL0181N The string representation of a...
15
by: Fritz Switzer | last post by:
I'd like to have a string assigned the value of a DateTime.AddMinutes(amount) so that the string is formatted in "HH:MM" format. For example: DateTime.Now.AddMinutes(30) returns "00:30" ...
3
by: Daniel M. | last post by:
I have a query that gets a string column containing a date, ex. 20040825. If I use the property builder to format the string to 08/25/2004 it does not work because it only accepts datetime columns....
0
by: vooose | last post by:
Suppose you have a DataTable which is part of a DataSet that has a column of type DateTime. When you call ds.GetXml() the date comes out looking something like this: ...
2
by: Brian Parker | last post by:
I am beginning to work with VB2005.NET and I'm getting some problems with string formatting converting an application from VB6. VB6 code:- sTradeDate = Format(pArray(4,i Record), "mmddyy") ...
18
by: Dirk Hagemann | last post by:
Hello, From a zone-file of a Microsoft Active Directory integrated DNS server I get the date/time of the dynamic update entries in a format, which is as far as I know the hours since january 1st...
8
by: yashiro | last post by:
Hello i Have a database in SQLserver. I am using php to connect to it and to retrieve data from that database. I have a table with a column (DATATIME) where i do : $result =...
8
by: joemacbusiness | last post by:
Hi All, How do I format printed data in python? I could not find this in the Python Reference Manual: http://docs.python.org/ref/print.html Nor could I find it in Matloff's great tutorial:...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.