Format Date to MM/DD/YYYY

Question

Format Date to MM/DD/YYYY

TheDirtyBird

Valued Member

Points: 70
More actions
June 4, 2008 at 12:52 pm

#187615

SELECT Top 1 ClassEndDate FROM Class WHERE ClassEndDate < GetDate() ORDER BY ClassEndDate DESC
My SELECT statement works fine, but I'm getting an output of 2008-05-04 00:00:00.000.
How can I format the date to MM/DD/YYYY? I tried using:
SELECT
CONVERT(datetime, GetDate(), 101)
That didn't work.
Also, this is for a .net application I've made. Not that it matters, here is my C# code:
protected void Page_Load(object sender, EventArgs e)
{
try
{
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["Table"].ToString()))
{
cn.Open();
SqlCommand cmd = new SqlCommand("SELECT Top 1 ClassEndDate FROM Class WHERE ClassEndDate < GetDate() ORDER BY ClassEndDate DESC", cn);
SqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
txtDate.Text = rdr["ClassEndDate"].ToString;
rdr.Close();
cn.Close();
}
}
catch
{
}
}

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

You are converting a Datetime to a Datetime, which has virtually no effect. You must convert the Datetime source into some string, like so:

SELECT

CONVERT(varchar(10), GetDate(), 101)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

TheDirtyBird Valued Member Points: 70 More actions · Answer 2

wow. Thanks for the quick and friendly reply.

This is a great site!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Thanks for the feedback!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 4

I highly recommend not bothering to format the data in SQL. Leave formatting to the presentation layer (front-end application). They're better at it, and can more easily have regional settings.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon