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

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.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

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/techinf...2000/books.asp

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.