Date to String - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Date to String

Author

Topic

Smiely.Kar
Starting Member

3 Posts

Posted - 2011-01-26 : 17:25:13

What I would like to do is,
Is the Date is NULL, return 'N/A'

ISNULL(replace(t.DateClosed, char(3), ''),'N/A') as DateClosed

This is changing my Date formate. I am using default date and it's returning Jan 26, 2010 format.

Please help

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-26 : 19:12:18

Dates are not strings; temporal data types exists! SQL is not COBOL or a spreadsheet. You are never supposed to format data for display in the database.
If you really want to be a bad programmer, here is a kludge:

COALESCE (CAST (closing_date AS CHAR(10)), 'N/A')

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Smiely.Kar
Starting Member

3 Posts

Posted - 2011-01-27 : 10:56:30

Thanks jcelko for replying. I just want to display that'N/A' on front end. I am not changing the database. I tried COALESCE, it's working put still it shows date in different format.
I have
<asp:Label ID="Label1" runat="server" Text='<%#Bind("DateClosed", "{0:HHmm/dd}") %>' Visible="true"></asp:Label>

on ASPX page. it's not following that 0:HHmm/dd format.

Please help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources