f



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

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
0
sohelcsc (9)
7/22/2003 8:03:35 AM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

3 Replies
1333 Views

Similar Articles

[PageSpeed] 26

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" <sohelcsc@yahoo.com> wrote in message
news:b1a93c73.0307220003.7f068543@posting.google.com...
> 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


0
7/22/2003 8:35:11 AM
Leader (sohelcsc@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, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
0
sommar (1290)
7/22/2003 9:42:55 PM
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
<sommar@algonet.se> wrote:

>Leader (sohelcsc@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.

0
Random
7/23/2003 3:00:21 AM
Reply:

Web resources about - what is equivalent of format(date) function of MS Access in MS Sql Server 2000 - comp.databases.ms-sqlserver

Antenna equivalent radius - Wikipedia, the free encyclopedia
... length of the circumference, and are vectors locating points along the circumference, and and are differentials segments along it. The equivalent ...

Equivalent Fractions on the App Store on iTunes
Get Equivalent Fractions on the App Store. See screenshots and ratings, and read customer reviews.

Telstra free data day sees mobile users download 1,841 terabytes, equivalent of 2.3 million movies
One industrious Telstra user in Sydney spent Sunday downloading 421 gigabytes of data to his phone, and he was not alone.

Intel: For Mainstream Gamers, Our IGPs Are Equivalent to Discrete GPUs
... where it was five years ago. We believe that the performance of Intel’s integrated graphics today, what we offer in the products […], is equivalent ...

Tim Cook: FBI is asking Apple to create 'software equivalent of cancer'
In a lengthy interview with ABC News anchor David Muir, Apple CEO Tim Cook reiterated that the repercussions of complying with FBI requests to ...

Tim Cook says FBI wants Apple to create the software equivalent of cancer’
Apple is pulling out all the PR stops as part of its broader campaign to help explain why it adamantly refuses to help the FBI unlock an iPhone ...

"Have we contributed to this culture that has turned American politics and the American political discourse ...
That's the question Marco Rubio said "we all need to take a step back" and ask. He adds: "Presidential candidates are now basically Twitter trolls." ...

Apple CEO Tim Cook: FBI's backdoor would be 'software equivalent of cancer'
... software that would make hundreds of millions of people insecure?" Cook said that what the FBI is requesting would be the "software equivalent ...

NexDock is the Equivalent of a $100 MacBook Air
In the last few years, we’ve seen a proliferation of tiny, pocket-sized PCs. Obviously, that includes smartphones, but we’re also talking about ...

Target Australia is wrapping R18+ games in the equivalent of brown paper
I remember reading once about how retailers voluntarily censored the album cover of hair metal doofuses, Ratt's, Out of the Cellar . The image ...

Resources last updated: 3/22/2016 11:11:38 AM