Need to convert Date from "YYYY-MM-DD 00:00:00.0000" to "MM/DD/YYYY&qu

Hello, I was asked to run a query to retrieve some data for my boss. The
query works just fine, however, my boss wants to import the data into his
excel spreadsheet, and the date values as displayed in the result set in
query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off
all of the time stamp crap. I tried some CONVERT/CAST functions to no avail
.
This is sort of urgent, PLEASE HELP!!
Thank you...
RGAlso, my date does need the forward slashes too (/) as in "mm/dd/yyyyy".
Thanks.
RG
Robert G wrote:
>Hello, I was asked to run a query to retrieve some data for my boss. The
>query works just fine, however, my boss wants to import the data into his
>excel spreadsheet, and the date values as displayed in the result set in
>query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
>Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off
>all of the time stamp crap. I tried some CONVERT/CAST functions to no avai
l.
>This is sort of urgent, PLEASE HELP!!
>Thank you...
>RG|||If its left in a datetime data type, the time portion will always be
returned. Instead, convert it to a string using something like the
following,
select convert(varchar(10), getdate(), 101)
--Brian
(Please reply to the newsgroups only.)
"Robert G via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:546B8E3963E09@.webservertalk.com...
> Hello, I was asked to run a query to retrieve some data for my boss. The
> query works just fine, however, my boss wants to import the data into his
> excel spreadsheet, and the date values as displayed in the result set in
> query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
> Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim
> off
> all of the time stamp crap. I tried some CONVERT/CAST functions to no
> avail.
> This is sort of urgent, PLEASE HELP!!
> Thank you...
> RG|||SQL Server does not have a "date only" data type, so you will need to jump
through some hoops...
Since your boss is importing the data into Excel, my first recommendation
would be to leave the data as it is, and just format it appropriately in
Excel. Excel can easily suppress the display of the time values, and it will
properly recognize the values as date values.
If that is not an option, you could try a kludge such as "SELECT
CONVERT(CHAR(8), DateColumn, 112), <other columns here> FROM UnknownTable",
which will output a character column formatted as 'YYYYMMDD'. Depending on
how you are making this output available to Excel, Excel may or may not
determine that this is a Date column, and may not provide proper sorting
functionality.
"Robert G via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:546B8E3963E09@.webservertalk.com...
> Hello, I was asked to run a query to retrieve some data for my boss. The
> query works just fine, however, my boss wants to import the data into his
> excel spreadsheet, and the date values as displayed in the result set in
> query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
> Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim
> off
> all of the time stamp crap. I tried some CONVERT/CAST functions to no
> avail.
> This is sort of urgent, PLEASE HELP!!
> Thank you...
> RG|||References: <546B8E3963E09@.webservertalk.com>
In-Reply-To: <546B8E3963E09@.webservertalk.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <#Wf$YLkuFHA.860@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.programming
NNTP-Posting-Host: 208.13.225.3
Path: TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Lines: 1
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:552942
in addition to the other comments - you could simply change the boss's
spreadsheet column to display date format. :)
Robert G via webservertalk.com wrote:

>Hello, I was asked to run a query to retrieve some data for my boss. The
>query works just fine, however, my boss wants to import the data into his
>excel spreadsheet, and the date values as displayed in the result set in
>query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
>Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off
>all of the time stamp crap. I tried some CONVERT/CAST functions to no avai
l.
>This is sort of urgent, PLEASE HELP!!
>Thank you...
>RG
>|||Hell yes, thanks Brian!!! That worked like a freakin charm !! I did the
following:
convert(varchar(10), [MyDateField], 101)
I'd really like to thank everyone else who responded as well !!! I hope I
can return the favor some day.
RG
Brian Lawton wrote:
>If its left in a datetime data type, the time portion will always be
>returned. Instead, convert it to a string using something like the
>following,
>select convert(varchar(10), getdate(), 101)
>
>[quoted text clipped - 10 lines]|||I know what it is like to have a boss who is a total idiot, but you
might wantot get him/her a copy of ISO-8601 and ask why he is smarter
than the entire world. I would love to hear his/her reply :)
The kludge for the moron is a CONVERT() in a VIEW.|||Hey thanks for your input. That does what you said it would, but excel
doesn't recognize it as a date format. Also, I could've parsed it in the
query and eventually ended up with what I needed, but it would've required a
lot more work than Brian's solution - which took like two seconds.
But thanks so much, I still learned from your response.
RG
Jeremy Williams wrote:
>SQL Server does not have a "date only" data type, so you will need to jump
>through some hoops...
>Since your boss is importing the data into Excel, my first recommendation
>would be to leave the data as it is, and just format it appropriately in
>Excel. Excel can easily suppress the display of the time values, and it wil
l
>properly recognize the values as date values.
>If that is not an option, you could try a kludge such as "SELECT
>CONVERT(CHAR(8), DateColumn, 112), <other columns here> FROM UnknownTable",
>which will output a character column formatted as 'YYYYMMDD'. Depending on
>how you are making this output available to Excel, Excel may or may not
>determine that this is a Date column, and may not provide proper sorting
>functionality.
>
>[quoted text clipped - 10 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200509/1