what is equivalent of format(date) function of MS Access in MS Sql Server 2000

[Leader]

Hi All,
I am facing a problem with a sql what i used in MS Access but its not
returning the same result in MS Sql Server 2000. Here i am giving the
sql:

SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHERE
MY_ID=1

The above sql in ACCESS return me the date in below format in one
column:

Friday 09, 2003

But in Sql server 2000 i am not getting the same format eventhough i
am using convert function, date part function etc.

Please if you find the solution would be helpful for me..

Thanks
Hoque

[John Bell]

Hi

If you field is a datetime, you can use the CONVERT function to convert it
to a formatted string. See books online for more information.

John

"Leader" <so******@yahoo.com> wrote in message
news:b1**************************@posting.google.c om...

Hi All,
I am facing a problem with a sql what i used in MS Access but its not
returning the same result in MS Sql Server 2000. Here i am giving the
sql:

SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHERE
MY_ID=1

The above sql in ACCESS return me the date in below format in one
column:

Friday 09, 2003

But in Sql server 2000 i am not getting the same format eventhough i
am using convert function, date part function etc.

Please if you find the solution would be helpful for me..

Thanks
Hoque

[Erland Sommarskog]

Leader (so******@yahoo.com) writes:

I am facing a problem with a sql what i used in MS Access but its not
returning the same result in MS Sql Server 2000. Here i am giving the
sql:

SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHERE
MY_ID=1

The above sql in ACCESS return me the date in below format in one
column:

Friday 09, 2003

But in Sql server 2000 i am not getting the same format eventhough i
am using convert function, date part function etc.

This gives you what you want:

select datename(dw, getdate()) + ' ' + datename(dd, getdate()) + ', ' +
datename(yyyy, getdate())

Well almost. You don't get any leading zero for a one-digit date.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Random]

I am not certain about the type, but something like
CONVERT(varchar,MY_DATE,112) should work....

The 112 part may be incorrect, look up CONVERT in the Query Analyzer
help file... It shows the format numbers.

On Tue, 22 Jul 2003 21:42:55 +0000 (UTC), Erland Sommarskog
<so****@algonet.se> wrote:

Leader (so******@yahoo.com) writes:

I am facing a problem with a sql what i used in MS Access but its not
returning the same result in MS Sql Server 2000. Here i am giving the
sql:

SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHERE
MY_ID=1

The above sql in ACCESS return me the date in below format in one
column:

Friday 09, 2003

But in Sql server 2000 i am not getting the same format eventhough i
am using convert function, date part function etc.

This gives you what you want:

select datename(dw, getdate()) + ' ' + datename(dd, getdate()) + ', ' +
datename(yyyy, getdate())

Well almost. You don't get any leading zero for a one-digit date.