By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,289 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,289 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Jul 20 '05 #2

P: n/a
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
Jul 20 '05 #3

P: n/a
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.


Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.