You need to find out who was responsible for that design, and have a talk
with him/her - out back. ;-)

--
Jeff Mason              Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: John Sek [mailto:air_salak@h...]
Sent: Thursday, March 06, 2003 1:44 AM
To: sql language
Subject: [sql_language] RE: substring for numeric data type


Thanks Jeff,

I always use datetime data type for my database design. However, most of
the time i need to maintain existing programs and dB used by my
organisation.

Your methods will definately help in my query. Thanks.

john


> You are aware that there is a datatype specifically designed to handle
dates?  If you had used it, you wouldn't have needed to post this question,
as there are several functions which allow you to manipulate dates in the
way you'd like.

The STR function takes an optional second argument which is the length of
the resultant string.  If not supplied, the default is 10.  Your string is
8
characters long, so that is why your results were offset.  Either supply
the
correct length, or the correct character offsets:

	SELECT substring(str(datefield,8),5,2)

or

	SELECT substring(str(datefield),7,2)

You could also CAST to a datetime and use the datepart function to extract
the month:

	SELECT datepart(m,CAST(datefield as datetime))

Or best of all, use the correct datatype for the data you are trying to
represent.
--
Jeff Mason              Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: John Sek [mailto:air_salak@h...]
Sent: Wednesday, March 05, 2003 9:57 AM
To: sql language
Subject: [sql_language] substring for numeric data type


hi all,

i have a date field defined in numeric data type. eg 21 August 2002 stored
as 20020821. Now i want to select the month of each records. i use this
method:
select substring(str(datefield),5,2) from table1;

from the sql statement, i don't get the month i wanted but some other
numbers.

I'm using ms sql 2000.
please help.

john