Date Format

[Tom Nowak]

I am saving a field to a SQL 2005 Express database in DateTime Format. I do
not want the 12:00:00 AM to appear at the end of this data, I just want the
Date to appear. How do I do this? Please help. Thanks.

 

[bruce barker]

when you select the date, convert it to a string of the format of your
choice. you can do it on the sql select or in your code behind.

select convert(varchar,mydate,101) as mydate
from mytable

note: sqlserver 2008 will have a date datatype that only holds the date.

-- bruce (sqlwork.com)

 

[Mark Rae [MVP]]

I am saving a field to a SQL 2005 Express database in DateTime Format. I
do
not want the 12:00:00 AM to appear at the end of this data, I just want
the
Date to appear. How do I do this? Please help. Thanks.

Not sure what you mean...

The "12:00:00 AM" is just part of SQL Server's default presentation format
for dates according to the locale you chose when you installed it and/or the
locale of the OS that it's running on.

Internally, dates are stored as numbers - the presentation is irrelevant...

 

[Tom Nowak]

So, if my query is:

SELECT [id], [date], [time], [employee] FROM [changes] WHERE ([date] = @date)

How do I modify this statement?

 

[Mark Rae [MVP]]

So, if my query is:

SELECT [id], [date], [time], [employee] FROM [changes] WHERE ([date] =
@date)

How do I modify this statement?

Are you really only interested in the way the date is displayed in SQL Query
Analyzer, or are you talking about changing the presentation of the date
when displayed on a web page...?

 

[Tom Nowak]

This is my query defined to an SQLDataAdapter. When the web page displays,
the date column contains the date and a time of 12:00:00. I dont want this
time displayed as I have time displayed in another column.

So, if my query is:

SELECT [id], [date], [time], [employee] FROM [changes] WHERE ([date] =
@date)

How do I modify this statement?

Are you really only interested in the way the date is displayed in SQL Query
Analyzer, or are you talking about changing the presentation of the date
when displayed on a web page...?

 

[Mark Rae [MVP]]

So, if my query is:

SELECT [id], [date], [time], [employee] FROM [changes] WHERE ([date] =
@date)

How do I modify this statement?

Are you really only interested in the way the date is displayed in SQL
Query
Analyzer, or are you talking about changing the presentation of the date
when displayed on a web page...?

This is my query defined to an SQLDataAdapter. When the web page
displays,
the date column contains the date and a time of 12:00:00. I dont want
this
time displayed as I have time displayed in another column.

That's what I suspected. This has nothing whatever to do with either SQL
Server or with your query...

What you need to do is to change the way the data is presented to the user
on the web page, not how it's fetched from the database.

How you do this will depend what control you're using to display the data.
You mention "another column", so I'm guessing you're using a GridView...?

In which case, just add the following to the date column:

DataFormatString="{0:ddd dd MMM yyyy}" HtmlEncode="false"

 

[gnewsgroup]

So, if my query is:
SELECT [id], [date], [time], [employee] FROM [changes] WHERE ([date] =
@date)
How do I modify this statement?
Are you really only interested in the way the date is displayed in SQL
Query
Analyzer, or are you talking about changing the presentation of the date
when displayed on a web page...?

This is my query defined to an SQLDataAdapter. When the web page
displays,
the date column contains the date and a time of 12:00:00. I dont want
this
time displayed as I have time displayed in another column.

That's what I suspected. This has nothing whatever to do with either SQL
Server or with your query...

What you need to do is to change the way the data is presented to the user
on the web page, not how it's fetched from the database.

How you do this will depend what control you're using to display the data.
You mention "another column", so I'm guessing you're using a GridView...?

In which case, just add the following to the date column:

DataFormatString="{0:ddd dd MMM yyyy}" HtmlEncode="false"

Or if it is gonna be printed on a Label or Literal control, use
ToShortDateString() method.

 

[Mark Rae [MVP]]

Or if it is gonna be printed on a Label or Literal control, use
ToShortDateString() method.

Why?

 

[gnewsgroup]

Why?

I thought that ToShortDateString() method will give you a date pattern
like what he wanted: MM/DD/YYYY, right?

 

[Mark Rae [MVP]]

I thought that ToShortDateString() method will give you a date pattern
like what he wanted: MM/DD/YYYY, right?

So, what date does 03/02/2008 represent?

 

[gnewsgroup]

So, what date does 03/02/2008 represent?

That must depends on the Locale setting of the application, right?

 

[Mark Rae [MVP]]

That must depend on the Locale setting of the application, right?

Correct or, more specifically, it will depend on the locale setting of the
webserver on which the web application is running, which means that it's
*ALWAYS* the wrong format for a web application...

It's a big ol' world, you know...

 

[gnewsgroup]

Correct or, more specifically, it will depend on the locale setting of the
webserver on which the web application is running, which means that it's
*ALWAYS* the wrong format for a web application...

It's a big ol' world, you know...

Then, the only thing I can think of is to manipulate it in the code
depending on where the query is coming from. Or, to be lazy, let the
end user decide on it.

 

[Mark Rae [MVP]]

Then, the only thing I can think of is to manipulate it in the code
depending on where the query is coming from. Or, to be lazy, let the
end user decide on it.

You're joking, obviously - for one thing, you can never reliably know where
the query "is coming from"...

And, just as likely,-the user could be travelling overseas on business and
visiting your site from an Internet cafe at an airport...

The only thing I can think of is to not use an unambiguous date format e.g.
dd MMM yyyy