Format date output

Hello,
I have a query that looks something like this:
select LEFT(dbo.Charges.service_date + SPACE(10), 10) AS ImmunizationDate
from dbo.charges
I need output to be in mm/dd/yyyy format without changing users date/time
settings. Service_date is of datetime data type.
Deki PAYou need to use the optional style parameter.
select convert(varchar(50),getdate(),101)
Your code will look something like

> select LEFT(CONVERT(varchar(50),dbo.Charges.service_date,101) + SPACE(10),
> 10) AS ImmunizationDate
> from dbo.charges
HTH. Ryan
"Deki" <Deki@.discussions.microsoft.com> wrote in message
news:1D07032A-EEB0-42BF-AA7D-CE683EAF1312@.microsoft.com...
> Hello,
> I have a query that looks something like this:
> select LEFT(dbo.Charges.service_date + SPACE(10), 10) AS ImmunizationDate
> from dbo.charges
> I need output to be in mm/dd/yyyy format without changing users date/time
> settings. Service_date is of datetime data type.
>
> --
> Deki PA
>|||You can use the Convert Function as in
SELECT CONVERT(VARCHAR,GETDATE(), 101)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Deki" <Deki@.discussions.microsoft.com> wrote in message
news:1D07032A-EEB0-42BF-AA7D-CE683EAF1312@.microsoft.com...
> Hello,
> I have a query that looks something like this:
> select LEFT(dbo.Charges.service_date + SPACE(10), 10) AS ImmunizationDate
> from dbo.charges
> I need output to be in mm/dd/yyyy format without changing users date/time
> settings. Service_date is of datetime data type.
>
> --
> Deki PA
>|||The best answer is to format the data on the client using whatever API you
have available.
Second best (if your UI is not possible to change) check the CONVERT
possibilities.
Third best, use datepart and string functions and roll your own:
select cast(datepart(m, getdate()) as varchar(2)) + '/' + cast(datepart(d,
getdate()) as varchar(2)) + '/' + right(cast(datepart(year, getdate()) as
char(4)),2)
Then you can produce any format you want. I didn't deal with single digit
months because you should be able to use:
select CONVERT (varchar(10),getdate(),1)
But if you need something like this, it can be handy to use datepart to
format a date
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Deki" <Deki@.discussions.microsoft.com> wrote in message
news:1D07032A-EEB0-42BF-AA7D-CE683EAF1312@.microsoft.com...
> Hello,
> I have a query that looks something like this:
> select LEFT(dbo.Charges.service_date + SPACE(10), 10) AS ImmunizationDate
> from dbo.charges
> I need output to be in mm/dd/yyyy format without changing users date/time
> settings. Service_date is of datetime data type.
>
> --
> Deki PA
>|||Thank you all!
--
Deki PA
"Louis Davidson" wrote:

> The best answer is to format the data on the client using whatever API you
> have available.
> Second best (if your UI is not possible to change) check the CONVERT
> possibilities.
> Third best, use datepart and string functions and roll your own:
> select cast(datepart(m, getdate()) as varchar(2)) + '/' + cast(datepart(d,
> getdate()) as varchar(2)) + '/' + right(cast(datepart(year, getdate()) as
> char(4)),2)
> Then you can produce any format you want. I didn't deal with single digit
> months because you should be able to use:
> select CONVERT (varchar(10),getdate(),1)
> But if you need something like this, it can be handy to use datepart to
> format a date
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "Deki" <Deki@.discussions.microsoft.com> wrote in message
> news:1D07032A-EEB0-42BF-AA7D-CE683EAF1312@.microsoft.com...
>
>