Formatting Dates as YYYY/MM/DD for a particular query

Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?
The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2))... Which seems so rediculous. There's gotta be a better way!select convert(varchar(10), getdate(), 111)|||Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?

The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2))... Which seems so rediculous. There's gotta be a better way!

With the caveat that it's almost ALWAYS better to handle formatting on the client side, you might try:

Convert(varchar(10), [YourDate], 120)

Regards,

hmscott|||select convert(varchar(10), getdate(), 111)
Thanks! You've bailed me out of another toughee...

Formatting a float in varchar but NOT in scientific notation

I'm trying to find a way to format a FLOAT variable into a varchar in
SQL Server 2000 but using CAST/CONVERT I can only get scientific
notation i.e. 1e+006 instead of 1000000 which isn't really what I
wanted.

Preferably the varchar would display the number to 2 decimal places
but I'd settle for integers only as this conversion isn't business
critical and is a nice to have for background information.

Casting to MONEY or NUMERIC before converting to a varchar works fine
for most cases but of course runs the risk of arithmetic overflow if
the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
knows of an easy way to test whether overflow will occur and therefore
to know not to convert it then that would be an option.

I appreciate SQL Server isn't great at formatting and it would be far
easier in the client code but code this is being performed as a
description of a very simple calculation in a trigger, all stored to
the database on the server side so there's no opportunity for client
intervention.

Example code:

declare @.testFloat float
select @.testFloat = 1000000.12

select convert(varchar(100),@.testFloat) -- gives 1e+006
select cast(@.testFloat as varchar(100)) -- gives 1e+006
select convert(varchar(100),cast(@.testFloat as money)) -- gives
1000000.12

select @.testFloat = 12345678905345633453453624453453524.123

select convert(varchar(100),cast(@.testFloat as money)) -- gives
arithmetic overflow error
select convert(varchar(100),cast(@.testFloat as numeric)) -- gives
arithmetic overflow error

Any suggestions welcome...

Cheers
DaveTry specifying the desired precision and scale on your decimal/numeric
declaration:

SELECT CONVERT(varchar(100), CAST(@.testFloat AS decimal(38,2)))

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312130327.4482a7a@.posting.google.co m...
> I'm trying to find a way to format a FLOAT variable into a varchar in
> SQL Server 2000 but using CAST/CONVERT I can only get scientific
> notation i.e. 1e+006 instead of 1000000 which isn't really what I
> wanted.
> Preferably the varchar would display the number to 2 decimal places
> but I'd settle for integers only as this conversion isn't business
> critical and is a nice to have for background information.
> Casting to MONEY or NUMERIC before converting to a varchar works fine
> for most cases but of course runs the risk of arithmetic overflow if
> the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
> knows of an easy way to test whether overflow will occur and therefore
> to know not to convert it then that would be an option.
> I appreciate SQL Server isn't great at formatting and it would be far
> easier in the client code but code this is being performed as a
> description of a very simple calculation in a trigger, all stored to
> the database on the server side so there's no opportunity for client
> intervention.
> Example code:
> declare @.testFloat float
> select @.testFloat = 1000000.12
> select convert(varchar(100),@.testFloat) -- gives 1e+006
> select cast(@.testFloat as varchar(100)) -- gives 1e+006
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> 1000000.12
> select @.testFloat = 12345678905345633453453624453453524.123
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> arithmetic overflow error
> select convert(varchar(100),cast(@.testFloat as numeric)) -- gives
> arithmetic overflow error
> Any suggestions welcome...
> Cheers
> Dave|||STR() function might help you.

SELECT STR(123.45, 6, 1)

Check BOL.

"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312130327.4482a7a@.posting.google.co m...
> I'm trying to find a way to format a FLOAT variable into a varchar in
> SQL Server 2000 but using CAST/CONVERT I can only get scientific
> notation i.e. 1e+006 instead of 1000000 which isn't really what I
> wanted.
> Preferably the varchar would display the number to 2 decimal places
> but I'd settle for integers only as this conversion isn't business
> critical and is a nice to have for background information.
> Casting to MONEY or NUMERIC before converting to a varchar works fine
> for most cases but of course runs the risk of arithmetic overflow if
> the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
> knows of an easy way to test whether overflow will occur and therefore
> to know not to convert it then that would be an option.
> I appreciate SQL Server isn't great at formatting and it would be far
> easier in the client code but code this is being performed as a
> description of a very simple calculation in a trigger, all stored to
> the database on the server side so there's no opportunity for client
> intervention.
> Example code:
> declare @.testFloat float
> select @.testFloat = 1000000.12
> select convert(varchar(100),@.testFloat) -- gives 1e+006
> select cast(@.testFloat as varchar(100)) -- gives 1e+006
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> 1000000.12
> select @.testFloat = 12345678905345633453453624453453524.123
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> arithmetic overflow error
> select convert(varchar(100),cast(@.testFloat as numeric)) -- gives
> arithmetic overflow error
> Any suggestions welcome...
> Cheers
> Dave|||Dan and Igor, both examples worked great, thanks very much.

SELECT CONVERT(varchar(100), CAST(@.testFloat AS decimal(38,2)))
SELECT STR(@.testFloat, 38, 2)

Cheers
Dave

Format number in stored procedure

I have one question about stored procedure. I will write expression
which I used and result what I want:
Cast(InvNumber as varchar)+'/'+cast(year(InvDate) as varchar) results
as 75/2006. I want it to result as 00075/2006. How can I do it?Here is an example, but this should be done at the front-end
declare @.InvNumber int
select @.InvNumber = 75
SELECT @.InvNumber, RIGHT('0000000000' + Cast(@.InvNumber as
varchar)+'/'+cast(year(getdate()) as varchar),10)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for your response,
I can not use code which you propose because I have many InvNumber not
only one. This number 75 was only sample number.
Regards,|||Sure you can, that was just an example
SELECT RIGHT('0000000000' + Cast(InvNumber as
varchar)+'/'+cast(year(InvDate) as varchar),10)
FROM ...
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I got the error message: Subquery returned more than 1 value. It is not
allowed etc.|||Can you show use the query you executed?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<lemes_m@.yahoo.com> wrote in message news:1147965460.658957.270150@.y43g2000cwc.googlegroups.com...
>I got the error message: Subquery returned more than 1 value. It is not
> allowed etc.
>

Format Dates in TSQL - My code below

Howdy,
Is this proc:
SELECT ID, CID, dtDate, strTime, dtRSVP,
CASE
WHEN dtRSVP > 1/1/1900 THEN
'RSVP by ' + CAST(MONTH(dtRSVP) as varchar(15)) + '/' + CAST(DAY(dtRSVP)
as varchar(15)) + '/' + CAST(YEAR(dtRSVP) as varchar(15))
ELSE
NULL
END AS niceRSVP
FROM tblDates
the best solution to get these results:
ID EID dtDate strTimes
dtRSVP niceRSVP
3 4 2005-12-01 00:00:00 1:00 PM to 3:00 PM 2005-11-25
00:00:00 RSVP by 11/25/2005
4 4 2005-12-02 00:00:00 12-4 PM
1900-01-01 00:00:00 NULL
Basically, I want the dates formatted like 11/25/2005, and empty date fields
to be NULL not 1/1/1900. Same goes for the dtDate and dtRSVP fields, but I
made the niceRSVP field to compensate.
Please advise!
Thanks!
David Lozzi
Web Applications Developer
dlozzi@.(remove-this)delphi-ts.comDavid,
Is it required to be a NULL or the text NULL. If the latter, the following
will work. See as example:
CREATE TABLE TBL_DATES
(ID INT NOT NULL,
DTVAL DATETIME )
GO
INSERT TBL_DATES(ID,DTVAL)
VALUES(1, '10/12/05')
INSERT TBL_DATES(ID,DTVAL)
VALUES(2, '')
INSERT TBL_DATES(ID)
VALUES(3)
GO
SELECT CASE
WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN 'FALSE NULL' ELSE
CONVERT(VARCHAR(10),DTVAL,101)
END AS 'DATE'
FROM TBL_DATES
HTH
Jerry
"David Lozzi" <DavidLozzi@.nospam.nospam> wrote in message
news:elUhO1eyFHA.3096@.TK2MSFTNGP10.phx.gbl...
> Howdy,
> Is this proc:
> SELECT ID, CID, dtDate, strTime, dtRSVP,
> CASE
> WHEN dtRSVP > 1/1/1900 THEN
> 'RSVP by ' + CAST(MONTH(dtRSVP) as varchar(15)) + '/' + CAST(DAY(dtRSVP)
> as varchar(15)) + '/' + CAST(YEAR(dtRSVP) as varchar(15))
> ELSE
> NULL
> END AS niceRSVP
> FROM tblDates
> the best solution to get these results:
> ID EID dtDate strTimes dtRSVP
> niceRSVP
> 3 4 2005-12-01 00:00:00 1:00 PM to 3:00 PM 2005-11-25
> 00:00:00 RSVP by 11/25/2005
> 4 4 2005-12-02 00:00:00 12-4 PM 1900-01-01 00:00:00
> NULL
> Basically, I want the dates formatted like 11/25/2005, and empty date
> fields to be NULL not 1/1/1900. Same goes for the dtDate and dtRSVP
> fields, but I made the niceRSVP field to compensate.
> Please advise!
> Thanks!
> --
> David Lozzi
> Web Applications Developer
> dlozzi@.(remove-this)delphi-ts.com
>
>|||Or this with NULL.
SELECT CASE
WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN
NULLIF('01/01/1900',DTVAL) ELSE
CONVERT(VARCHAR(10),DTVAL,101)
END AS 'DATE'
FROM TBL_DATES
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e634GCfyFHA.2064@.TK2MSFTNGP09.phx.gbl...
> David,
> Is it required to be a NULL or the text NULL. If the latter, the
> following will work. See as example:
> CREATE TABLE TBL_DATES
> (ID INT NOT NULL,
> DTVAL DATETIME )
> GO
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(1, '10/12/05')
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(2, '')
> INSERT TBL_DATES(ID)
> VALUES(3)
> GO
> SELECT CASE
> WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN 'FALSE NULL' ELSE
> CONVERT(VARCHAR(10),DTVAL,101)
> END AS 'DATE'
> FROM TBL_DATES
>
> HTH
> Jerry
> "David Lozzi" <DavidLozzi@.nospam.nospam> wrote in message
> news:elUhO1eyFHA.3096@.TK2MSFTNGP10.phx.gbl...
>|||Or even more succinctly:
SELECT
niceRSVP = 'RSVP by ' + CONVERT(varchar(15),dtRSVP,101)
FROM
tblDates
A null value in dtRSVP will pass through CONVERT as a null, and adding
anything to a null equals null.
JR
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e634GCfyFHA.2064@.TK2MSFTNGP09.phx.gbl...
> David,
> Is it required to be a NULL or the text NULL. If the latter, the
> following will work. See as example:
> CREATE TABLE TBL_DATES
> (ID INT NOT NULL,
> DTVAL DATETIME )
> GO
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(1, '10/12/05')
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(2, '')
> INSERT TBL_DATES(ID)
> VALUES(3)
> GO
> SELECT CASE
> WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN 'FALSE NULL' ELSE
> CONVERT(VARCHAR(10),DTVAL,101)
> END AS 'DATE'
> FROM TBL_DATES
>
> HTH
> Jerry
> "David Lozzi" <DavidLozzi@.nospam.nospam> wrote in message
> news:elUhO1eyFHA.3096@.TK2MSFTNGP10.phx.gbl...
>|||Jim,
How does this code account for the NULL if 01/01/1900?
HTH
Jerry
"Jim Ross" <jratwork_at_hotmail.com@.nowhwere.com> wrote in message
news:OMTaUPfyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Or even more succinctly:
> SELECT
> niceRSVP = 'RSVP by ' + CONVERT(varchar(15),dtRSVP,101)
> FROM
> tblDates
> A null value in dtRSVP will pass through CONVERT as a null, and adding
> anything to a null equals null.
> JR
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e634GCfyFHA.2064@.TK2MSFTNGP09.phx.gbl...
>