Date Formatting

1 Jul 2005 5:40 PM

tshad

This is always annoying. I am always trying to remember the best ways to
display my date and cannot remember the formats or how to set up a custom
format in Sql.

At the moment, I am using DateDisplay = convert(varchar,e.DateSent,0) to
get:

Jun 22 2005 4:20AM

I want to change this slighty to display it in the format of:

Monday, June 12, 2005 8:32 am

I also want to display the time as:

8:32 am

How would I do that?

Thanks,

Tom

1 Jul 2005 6:04 PM

Foo Man Chew

Display where? Can't you do this in the client language, e.g. VBScript has
a very powerful formatdatetime() function, VB has format(), etc.

Show quote

"tshad" <tscheider***@ftsolutions.com> wrote in message
news:ukoqCQmfFHA.2156@TK2MSFTNGP14.phx.gbl...
> This is always annoying. I am always trying to remember the best ways to
> display my date and cannot remember the formats or how to set up a custom
> format in Sql.
>
> At the moment, I am using DateDisplay = convert(varchar,e.DateSent,0) to
> get:
>
> Jun 22 2005 4:20AM
>
> I want to change this slighty to display it in the format of:
>
> Monday, June 12, 2005 8:32 am
>
> I also want to display the time as:
>
> 8:32 am
>
> How would I do that?
>
> Thanks,
>
> Tom
>

1 Jul 2005 6:05 PM

Bill Reed

Lots of help available - BOL is 1st choice but I keep this bit of sql
available as quick list. Just run it and choose the convert option you like
for code.

/*******************************/
/**** Date format Options ****/
/*******************************/
select getdate(), --2005-06-30 09:53:33.417
CAST(getdate() AS char(17)) as "Cast Dt", --Jun 30 2005 9:53
CONVERT(VARCHAR, getdate(), 100) AS Y100, --Jun 30 2005 9:53AM
CONVERT(VARCHAR, getdate(), 101) AS Y101, --06/30/2005
CONVERT(VARCHAR, getdate(), 102) AS Y102, --2005.06.30
CONVERT(VARCHAR, getdate(), 103) AS Y103, --30/06/2005
CONVERT(VARCHAR, getdate(), 104) AS Y104, --30.06.2005
CONVERT(VARCHAR, getdate(), 105) AS Y105, --30-06-2005
CONVERT(VARCHAR, getdate(), 106) AS Y106, --30 Jun 2005
CONVERT(VARCHAR, getdate(), 107) AS Y107, --Jun 30, 2005
CONVERT(VARCHAR, getdate(), 108) AS Y108, --09:53:33
CONVERT(VARCHAR, getdate(), 109) AS Y109, --Jun 30 2005 9:53:33:417AM
CONVERT(VARCHAR, getdate(), 110) AS Y110, --06-30-2005
CONVERT(VARCHAR, getdate(), 111) AS Y111, --2005/06/30
CONVERT(VARCHAR, getdate(), 112) AS Y112, --20050630
CONVERT(VARCHAR, getdate(), 113) AS Y113, --30 Jun 2005 09:53:33:417
CONVERT(VARCHAR, getdate(), 114) AS Y114 --09:53:33:417

Show quote

"tshad" wrote:

> This is always annoying. I am always trying to remember the best ways to
> display my date and cannot remember the formats or how to set up a custom
> format in Sql.
>
> At the moment, I am using DateDisplay = convert(varchar,e.DateSent,0) to
> get:
>
> Jun 22 2005 4:20AM
>
> I want to change this slighty to display it in the format of:
>
> Monday, June 12, 2005 8:32 am
>
> I also want to display the time as:
>
> 8:32 am
>
> How would I do that?
>
> Thanks,
>
> Tom
>
>
>

1 Jul 2005 7:59 PM

tshad

Show quote

"Bill Reed" <BillR***@discussions.microsoft.com> wrote in message
news:68A208E9-163C-4B56-AB8B-067A7BC64CA2@microsoft.com...
> Lots of help available - BOL is 1st choice but I keep this bit of sql
> available as quick list. Just run it and choose the convert option you
> like
> for code.
>
> /*******************************/
> /**** Date format Options ****/
> /*******************************/
> select getdate(), --2005-06-30 09:53:33.417
> CAST(getdate() AS char(17)) as "Cast Dt", --Jun 30 2005 9:53
> CONVERT(VARCHAR, getdate(), 100) AS Y100, --Jun 30 2005 9:53AM
> CONVERT(VARCHAR, getdate(), 101) AS Y101, --06/30/2005
> CONVERT(VARCHAR, getdate(), 102) AS Y102, --2005.06.30
> CONVERT(VARCHAR, getdate(), 103) AS Y103, --30/06/2005
> CONVERT(VARCHAR, getdate(), 104) AS Y104, --30.06.2005
> CONVERT(VARCHAR, getdate(), 105) AS Y105, --30-06-2005
> CONVERT(VARCHAR, getdate(), 106) AS Y106, --30 Jun 2005
> CONVERT(VARCHAR, getdate(), 107) AS Y107, --Jun 30, 2005
> CONVERT(VARCHAR, getdate(), 108) AS Y108, --09:53:33
> CONVERT(VARCHAR, getdate(), 109) AS Y109, --Jun 30 2005 9:53:33:417AM
> CONVERT(VARCHAR, getdate(), 110) AS Y110, --06-30-2005
> CONVERT(VARCHAR, getdate(), 111) AS Y111, --2005/06/30
> CONVERT(VARCHAR, getdate(), 112) AS Y112, --20050630
> CONVERT(VARCHAR, getdate(), 113) AS Y113, --30 Jun 2005 09:53:33:417
> CONVERT(VARCHAR, getdate(), 114) AS Y114 --09:53:33:417

But how do you do a custom date, like the 2 I showed, as they don't fit into
the pre-formated formats.

Thanks,

Tom
Show quote

>
>
>
> "tshad" wrote:
>
>> This is always annoying. I am always trying to remember the best ways to
>> display my date and cannot remember the formats or how to set up a custom
>> format in Sql.
>>
>> At the moment, I am using DateDisplay = convert(varchar,e.DateSent,0) to
>> get:
>>
>> Jun 22 2005 4:20AM
>>
>> I want to change this slighty to display it in the format of:
>>
>> Monday, June 12, 2005 8:32 am
>>
>> I also want to display the time as:
>>
>> 8:32 am
>>
>> How would I do that?
>>
>> Thanks,
>>
>> Tom
>>
>>
>>

1 Jul 2005 8:19 PM

Foo Man Chew

Again, have you looked at the capabilities of the *CLIENT* language, where
presentation takes place?

Personally, I think this is a better place to do this kind of thing. Why do
you think this capability isn't already there? Because client languages are
more than capable of altering the date any way they want.

Of course, there are kludges, such as using SUBSTRING() and DATENAME()
against several converted sections of the date. But what is the advantage?

Show quote

"tshad" <tscheider***@ftsolutions.com> wrote in message
news:%23pO7fdnfFHA.2424@TK2MSFTNGP09.phx.gbl...
> "Bill Reed" <BillR***@discussions.microsoft.com> wrote in message
> news:68A208E9-163C-4B56-AB8B-067A7BC64CA2@microsoft.com...
>> Lots of help available - BOL is 1st choice but I keep this bit of sql
>> available as quick list. Just run it and choose the convert option you
>> like
>> for code.
>>
>> /*******************************/
>> /**** Date format Options ****/
>> /*******************************/
>> select getdate(), --2005-06-30 09:53:33.417
>> CAST(getdate() AS char(17)) as "Cast Dt", --Jun 30 2005 9:53
>> CONVERT(VARCHAR, getdate(), 100) AS Y100, --Jun 30 2005 9:53AM
>> CONVERT(VARCHAR, getdate(), 101) AS Y101, --06/30/2005
>> CONVERT(VARCHAR, getdate(), 102) AS Y102, --2005.06.30
>> CONVERT(VARCHAR, getdate(), 103) AS Y103, --30/06/2005
>> CONVERT(VARCHAR, getdate(), 104) AS Y104, --30.06.2005
>> CONVERT(VARCHAR, getdate(), 105) AS Y105, --30-06-2005
>> CONVERT(VARCHAR, getdate(), 106) AS Y106, --30 Jun 2005
>> CONVERT(VARCHAR, getdate(), 107) AS Y107, --Jun 30, 2005
>> CONVERT(VARCHAR, getdate(), 108) AS Y108, --09:53:33
>> CONVERT(VARCHAR, getdate(), 109) AS Y109, --Jun 30 2005 9:53:33:417AM
>> CONVERT(VARCHAR, getdate(), 110) AS Y110, --06-30-2005
>> CONVERT(VARCHAR, getdate(), 111) AS Y111, --2005/06/30
>> CONVERT(VARCHAR, getdate(), 112) AS Y112, --20050630
>> CONVERT(VARCHAR, getdate(), 113) AS Y113, --30 Jun 2005 09:53:33:417
>> CONVERT(VARCHAR, getdate(), 114) AS Y114 --09:53:33:417
>
> But how do you do a custom date, like the 2 I showed, as they don't fit
> into the pre-formated formats.
>
> Thanks,
>
> Tom
>>
>>
>>
>> "tshad" wrote:
>>
>>> This is always annoying. I am always trying to remember the best ways
>>> to
>>> display my date and cannot remember the formats or how to set up a
>>> custom
>>> format in Sql.
>>>
>>> At the moment, I am using DateDisplay = convert(varchar,e.DateSent,0) to
>>> get:
>>>
>>> Jun 22 2005 4:20AM
>>>
>>> I want to change this slighty to display it in the format of:
>>>
>>> Monday, June 12, 2005 8:32 am
>>>
>>> I also want to display the time as:
>>>
>>> 8:32 am
>>>
>>> How would I do that?
>>>
>>> Thanks,
>>>
>>> Tom
>>>
>>>
>>>
>
>

1 Jul 2005 8:17 PM

Jens Süßmeyer

First to break down the illusions, building user defined formating dates on
SQL Server is a pain. But in fact that isn´t the work SQL Server is for. YOu
should always consider doing that kind of work on your frontend rather than
in SQL Server.Your frontend (depends on platform) has normally rich
functions to do that.

If you really wnat to do that, you should consider doing that on your own
with a user defined function, where you cop the important parts from the
dates, format them chop them again till to the end you get a usable date
format for you. Because functions are reusable that should be your way not
to double,triple,... your work.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Show quote

"tshad" <tscheider***@ftsolutions.com> schrieb im Newsbeitrag
news:ukoqCQmfFHA.2156@TK2MSFTNGP14.phx.gbl...
> This is always annoying. I am always trying to remember the best ways to
> display my date and cannot remember the formats or how to set up a custom
> format in Sql.
>
> At the moment, I am using DateDisplay = convert(varchar,e.DateSent,0) to
> get:
>
> Jun 22 2005 4:20AM
>
> I want to change this slighty to display it in the format of:
>
> Monday, June 12, 2005 8:32 am
>
> I also want to display the time as:
>
> 8:32 am
>
> How would I do that?
>
> Thanks,
>
> Tom
>