formatting dates

Hi, I was wondering if there is a way to output a special format for dates in SELECT statements. Currently, my date field is returning my date in this format:
2004-01-19 00:00:00.000
but I want it to be like this:
1/19/04 or 1/19/2004
Is there a way to do this?
Thanks.Try this:

SELECT CONVERT(char(10),getdate(),103)|||Please.. use the 101 code for the date format..

like this:

SELECT CONVERT(char(10),getdate(),101)

the other example is another format.

You can consult all the date format in the SQL SERVER BOOKS ON LINE!.|||But doesn't that just return the current date?
Here's my SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, checkedOutDate, ReturnedDate FROM Checkouts";

checkedOutDate and ReturnedDate are the fields I want to format. How would this work with getdate()?|||Just replace the getdate() with checkedOutDate. Do the same again with ReturnedDate (a separate query).

Originally posted by domiflichi
But doesn't that just return the current date?
Here's my SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, checkedOutDate, ReturnedDate FROM Checkouts";

checkedOutDate and ReturnedDate are the fields I want to format. How would this work with getdate()?|||Look at my post from yesterday. I had the same question.
exdter|||That's what I thought you're supposed to do. But when I do that...here's my new SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, CONVERT(char(10),checkedOutDate,101), ReturnedDate FROM Checkouts

I get an error when trying to display my results of the query when encountering that field:

Warning: odbc_result(): Field checkedoutdate not found in d:\web\territories\index.php on line 173

If it helps: my webserver is Win2k Pro running Apache and PHP, and my SQL server is on Win2K server and it's actually MSDE 2000.|||Originally posted by exdter
Look at my post from yesterday. I had the same question.
exdter

I'm getting the same error message on my query:

Warning: odbc_result(): Field checkedoutdate not found in d:\web\territories\index.php on line 174

Here's the SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),103), ReturnedDate FROM Checkouts

Why isn't this working for me?
Thanks,
domiflichi|||Try this

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP
, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),103) AS checkedOutDate, ReturnedDate FROM Checkouts|||Thank you Brett!...it worked! And thank you to everybody else for getting me through each step, and being patient with this newbie. And thank you all for such quick replies!

Formatting a float in varchar but NOT in scientific notation

I have come across another forum that deals (to a degree) with my issue
but i've got some extra special circumstances.
This below formats a float in Varchar, avoiding scientific notation:
SELECT STR(@.testFloat, 38, 2)
My problem is that I'm never sure how large my scale or precision needs
to be or even if I'm dealing with an integer or a float as I am
enumerating through columns, taking the float/int/varchar value and
putting it into a varchar column in a destination table.
I want to just say "enter into the destination varchar column the exact
same value as what was in the source table (whether it has 4 decimal
places, no decimal places or isn't even numeric).
For example, the above (@.testFloat, 38, 2) is converting my integer
such a 1856 to 1856.00 (argh!!).
anyone?
mikeHi
See other post..
John
"blomm" wrote:
> I have come across another forum that deals (to a degree) with my issue
> but i've got some extra special circumstances.
> This below formats a float in Varchar, avoiding scientific notation:
> SELECT STR(@.testFloat, 38, 2)
> My problem is that I'm never sure how large my scale or precision needs
> to be or even if I'm dealing with an integer or a float as I am
> enumerating through columns, taking the float/int/varchar value and
> putting it into a varchar column in a destination table.
> I want to just say "enter into the destination varchar column the exact
> same value as what was in the source table (whether it has 4 decimal
> places, no decimal places or isn't even numeric).
> For example, the above (@.testFloat, 38, 2) is converting my integer
> such a 1856 to 1856.00 (argh!!).
> anyone?
> mike
>

Formatting a float in varchar but NOT in scientific notation

I have come across another forum that deals (to a degree) with my issue
but i've got some extra special circumstances.
This below formats a float in Varchar, avoiding scientific notation:
SELECT STR(@.testFloat, 38, 2)
My problem is that I'm never sure how large my scale or precision needs
to be or even if I'm dealing with an integer or a float as I am
enumerating through columns, taking the float/int/varchar value and
putting it into a varchar column in a destination table.
I want to just say "enter into the destination varchar column the exact
same value as what was in the source table (whether it has 4 decimal
places, no decimal places or isn't even numeric).
For example, the above (@.testFloat, 38, 2) is converting my integer
such a 1856 to 1856.00 (argh!!).
anyone?
mikeHi
Does CONVERT with a style of 0 work better? e.g.
SELECT CONVERT(varchar(38),col,0) AS DecStr
FROM
( SELECT CAST(1856 as Float) as col
UNION ALL SELECT CAST(1856.09 as Float) ) A
DecStr
---
1856
1856.09
John
"blomm" wrote:
> I have come across another forum that deals (to a degree) with my issue
> but i've got some extra special circumstances.
> This below formats a float in Varchar, avoiding scientific notation:
> SELECT STR(@.testFloat, 38, 2)
> My problem is that I'm never sure how large my scale or precision needs
> to be or even if I'm dealing with an integer or a float as I am
> enumerating through columns, taking the float/int/varchar value and
> putting it into a varchar column in a destination table.
> I want to just say "enter into the destination varchar column the exact
> same value as what was in the source table (whether it has 4 decimal
> places, no decimal places or isn't even numeric).
> For example, the above (@.testFloat, 38, 2) is converting my integer
> such a 1856 to 1856.00 (argh!!).
> anyone?
> mike
>

Formatting a float in varchar but NOT in scientific notation

I have come across another forum that deals (to a degree) with my issue
but i've got some extra special circumstances.
This below formats a float in Varchar, avoiding scientific notation:
SELECT STR(@.testFloat, 38, 2)
My problem is that I'm never sure how large my scale or precision needs
to be or even if I'm dealing with an integer or a float as I am
enumerating through columns, taking the float/int/varchar value and
putting it into a varchar column in a destination table.
I want to just say "enter into the destination varchar column the exact
same value as what was in the source table (whether it has 4 decimal
places, no decimal places or isn't even numeric).
For example, the above (@.testFloat, 38, 2) is converting my integer
such a 1856 to 1856.00 (argh!!).
anyone?
mikeHi
Does CONVERT with a style of 0 work better? e.g.
SELECT CONVERT(varchar(38),col,0) AS DecStr
FROM
( SELECT CAST(1856 as Float) as col
UNION ALL SELECT CAST(1856.09 as Float) ) A
DecStr
---
1856
1856.09
John
"blomm" wrote:

> I have come across another forum that deals (to a degree) with my issue
> but i've got some extra special circumstances.
> This below formats a float in Varchar, avoiding scientific notation:
> SELECT STR(@.testFloat, 38, 2)
> My problem is that I'm never sure how large my scale or precision needs
> to be or even if I'm dealing with an integer or a float as I am
> enumerating through columns, taking the float/int/varchar value and
> putting it into a varchar column in a destination table.
> I want to just say "enter into the destination varchar column the exact
> same value as what was in the source table (whether it has 4 decimal
> places, no decimal places or isn't even numeric).
> For example, the above (@.testFloat, 38, 2) is converting my integer
> such a 1856 to 1856.00 (argh!!).
> anyone?
> mike
>