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