Date Format

How can you maintain consistance in generating output for a DATE datatype?
example, JAN, when CAST as a varchar it becomes 1, NOT 01Use function CONVERT instead.
Example:
select convert(varchar, getdate(), 126)
AMB
"Jim Yurt" wrote:

> How can you maintain consistance in generating output for a DATE datatype?
> example, JAN, when CAST as a varchar it becomes 1, NOT 01|||Can you elaborate a bit more? Are you saying that you want a particular outp
ut string format which
isn't available in the 3:rd parameter to the CONVERT function? Or do you wan
t to convert into a
string format which SQL Server later will be able to convert into datetime s
afely regardless of
language settings?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jim Yurt" <jyurt@.odh.oh.gov> wrote in message
news:A2EF5257-1A59-4C55-ADAD-2EAE71CB6ED8@.microsoft.com...
> How can you maintain consistance in generating output for a DATE datatype?
> example, JAN, when CAST as a varchar it becomes 1, NOT 01|||if I do something like this;
ELSE(Month(Convert(varchar, DATEFIELD, 126)))
for months up to October (10th) month, my output is 1 char wide, I would
like the 0 as a place holder, so to speak...
jan = 01 NOT 1
feb = 02 NOT 2...
need fixed format for output
"Tibor Karaszi" wrote:

> Can you elaborate a bit more? Are you saying that you want a particular ou
tput string format which
> isn't available in the 3:rd parameter to the CONVERT function? Or do you w
ant to convert into a
> string format which SQL Server later will be able to convert into datetime
safely regardless of
> language settings?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jim Yurt" <jyurt@.odh.oh.gov> wrote in message
> news:A2EF5257-1A59-4C55-ADAD-2EAE71CB6ED8@.microsoft.com...
>
>|||> ELSE(Month(Convert(varchar, DATEFIELD, 126)))
Is above the code you are actually executing? Only specifying varchar, witho
ut length, is same as
saying varchar(1), except in CAST and CONVERT where it defaults to 30 (for s
ome strange reason).
Anyhow, always specify the length.
Also, what datatype s DATEFIELD of? Datetime? If so, no need for the CONVERT
function at all.
Your problem is that the Month function returns an integer, not a string. Th
e number 01 and 1 is the
same number. We still don't know what you are doing with the number returned
from the expression. Is
it to be compared to a string in which you have leading zeroes? If so, why d
o you have those numbers
in a string datatype?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jim Yurt" <jyurt@.odh.oh.gov> wrote in message
news:7A4CE644-C87E-443E-8E49-7349038BCF99@.microsoft.com...
> if I do something like this;
> ELSE(Month(Convert(varchar, DATEFIELD, 126)))
> for months up to October (10th) month, my output is 1 char wide, I would
> like the 0 as a place holder, so to speak...
> jan = 01 NOT 1
> feb = 02 NOT 2...
> need fixed format for output
> "Tibor Karaszi" wrote:
>|||DATEFIELD (for the example) is datetime (datatype)...and to your point, MONT
H
return an integer, that's what I don't want.
I need a way to return the CHAR value of the MONTH from a datetime field...
"Tibor Karaszi" wrote:

> Is above the code you are actually executing? Only specifying varchar, wit
hout length, is same as
> saying varchar(1), except in CAST and CONVERT where it defaults to 30 (for
some strange reason).
> Anyhow, always specify the length.
> Also, what datatype s DATEFIELD of? Datetime? If so, no need for the CONVE
RT function at all.
> Your problem is that the Month function returns an integer, not a string.
The number 01 and 1 is the
> same number. We still don't know what you are doing with the number return
ed from the expression. Is
> it to be compared to a string in which you have leading zeroes? If so, why
do you have those numbers
> in a string datatype?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jim Yurt" <jyurt@.odh.oh.gov> wrote in message
> news:7A4CE644-C87E-443E-8E49-7349038BCF99@.microsoft.com...
>
>|||Allow me to rephrase your requirement - return the month of a datetime value
as a char(2) value with leading zeros. When you phrase it that way (and
when it exactly defines what you want), the solution becomes much easier to
find.
select convert(char(2), getdate(), 101)
"Jim Yurt" <jyurt@.odh.oh.gov> wrote in message
news:2159CCA0-3841-4436-8356-782E18E28821@.microsoft.com...
> DATEFIELD (for the example) is datetime (datatype)...and to your point,
MONTH
> return an integer, that's what I don't want.
> I need a way to return the CHAR value of the MONTH from a datetime
field...
> "Tibor Karaszi" wrote:
>
without length, is same as
(for some strange reason).
CONVERT function at all.
string. The number 01 and 1 is the
returned from the expression. Is
why do you have those numbers
would
particular output string format
you want to convert into a
datetime safely regardless of
datatype?|||Thanks for making more sense of my 'query'...
indeed what I was looking for was something like this;
convert(char(2), DATEFIELD, 101) + convert(char(2), DATEFIELD, 103) +
convert(char(4), DATEFILED, 126)
which was much better than this;
replace(replace(substring(convert(varcha
r(15), DATEFIELD, 126),5,3) +
substring(convert(varchar(15), DATEFIELD, 126),9,3) +
substring(convert(varchar(15), DATEFIELD, 126),1,4), 'T', ''), '-', '')
thanks for the help everyone!
"Scott Morris" wrote:

> Allow me to rephrase your requirement - return the month of a datetime val
ue
> as a char(2) value with leading zeros. When you phrase it that way (and
> when it exactly defines what you want), the solution becomes much easier t
o
> find.
> select convert(char(2), getdate(), 101)
> "Jim Yurt" <jyurt@.odh.oh.gov> wrote in message
> news:2159CCA0-3841-4436-8356-782E18E28821@.microsoft.com...
> MONTH
> field...
> without length, is same as
> (for some strange reason).
> CONVERT function at all.
> string. The number 01 and 1 is the
> returned from the expression. Is
> why do you have those numbers
> would
> particular output string format
> you want to convert into a
> datetime safely regardless of
> datatype?
>
>|||You will get same result with:
select replace(convert(varchar(10), DATEFIELD, 101), '/', '')
go
AMB
"Jim Yurt" wrote:
> Thanks for making more sense of my 'query'...
> indeed what I was looking for was something like this;
>
> convert(char(2), DATEFIELD, 101) + convert(char(2), DATEFIELD, 103) +
> convert(char(4), DATEFILED, 126)
> which was much better than this;
>
> replace(replace(substring(convert(varcha
r(15), DATEFIELD, 126),5,3) +
> substring(convert(varchar(15), DATEFIELD, 126),9,3) +
> substring(convert(varchar(15), DATEFIELD, 126),1,4), 'T', ''), '-', '')
> thanks for the help everyone!
> "Scott Morris" wrote:
>|||And a last comment - formatting of datetime values is best done on the
client (where language/regional issues are more appropriately handled).
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:01094609-D7E9-4C70-9AB6-A2DB89157AE5@.microsoft.com...
> You will get same result with:
> select replace(convert(varchar(10), DATEFIELD, 101), '/', '')
> go
>
> AMB
> "Jim Yurt" wrote:
>
value
(and
easier to
point,
varchar,
30
the
number
so,
I
Or do
into
DATE