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

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

Post by sohelcs » Wed, 23 Jul 2003 17:03:35


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
 
 
 

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

Post by John Bel » Wed, 23 Jul 2003 17:35:11

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

 
 
 

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

Post by Erland Som » Thu, 24 Jul 2003 06:42:55

Leader ( XXXX@XXXXX.COM ) writes:

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, XXXX@XXXXX.COM

Books Online for SQL Server SP3 at
http://www.yqcomputer.com/
 
 
 

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

Post by Rando » Thu, 24 Jul 2003 12:00:21

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