Printable View
what i am trying to do is change data in a column that has four or three characters to date e.g 199 to 1/99 or 0400 to 4/00
I got syntax error converting datetime from character string.
i must be doing something wrong
declare
@date1 datetime,
@date2 varchar(12)
select @date1= Effect_Code_Change from citynews
set @date1 = '15/00'
select @date2 = convert(varchar(12),@date1,101)
select substring(@date2,4,5)
I suggest using a regular expression, but to do this you might have to
put that into Perl.
here is the basic idea
search for *[0-9][99|00|other years]
then you can insert the / between [0-9] and [99|00|other years]
I hope this helps, but you may not want to do this with regular expressions and perl though,
because if you do then you will have to create another database
------------
elsie at 6/30/00 10:08:18 AM
what i am trying to do is change data in a column that has four or three characters to date e.g 199 to 1/99 or 0400 to 4/00
I got syntax error converting datetime from character string.
i must be doing something wrong
declare
@date1 datetime,
@date2 varchar(12)
select @date1= Effect_Code_Change from citynews
set @date1 = '15/00'
select @date2 = convert(varchar(12),@date1,101)
select substring(@date2,4,5)
I know that there has to be an easier way than this, but here is a statement that will convert a three or four character number into a date. Given only a month and year, it assumes the day is 1. This statement will convert it to an actual datetime datatype. For this sample, the table is named "Test" and the character field that contains the number (i.e. 199, 0400, etc) is named aDate)
SELECT
(CONVERT(smallint, aDate) / 100),
convert(smallint,((CONVERT(smallint, aDate) * .01) - (CONVERT(smallint, aDate) / 100)) * 100),
convert(smalldatetime,convert(char(2),(CONVERT(sma llint, aDate) / 100))+ "/1/" + convert(char(2),convert(smallint,((CONVERT(smallin t, aDate) * .01) - (CONVERT(smallint, aDate) / 100)) * 100)))
FROM test
------------
elsie at 6/30/00 10:08:18 AM
what i am trying to do is change data in a column that has four or three characters to date e.g 199 to 1/99 or 0400 to 4/00
I got syntax error converting datetime from character string.
i must be doing something wrong
declare
@date1 datetime,
@date2 varchar(12)
select @date1= Effect_Code_Change from citynews
set @date1 = '15/00'
select @date2 = convert(varchar(12),@date1,101)
select substring(@date2,4,5)
Sorry, I left too many expressions in there. Here is the statement to convert to date.
SELECT
convert(smalldatetime,convert(char(2),(CONVERT(sma llint, aDate) / 100))+ "/1/" + convert(char(2),convert(smallint,((CONVERT(smallin t, aDate) * .01) - (CONVERT(smallint, aDate) / 100)) * 100)))
FROM test
------------
babreu at 6/30/00 1:51:56 PM
I know that there has to be an easier way than this, but here is a statement that will convert a three or four character number into a date. Given only a month and year, it assumes the day is 1. This statement will convert it to an actual datetime datatype. For this sample, the table is named "Test" and the character field that contains the number (i.e. 199, 0400, etc) is named aDate)
SELECT
(CONVERT(smallint, aDate) / 100),
convert(smallint,((CONVERT(smallint, aDate) * .01) - (CONVERT(smallint, aDate) / 100)) * 100),
convert(smalldatetime,convert(char(2),(CONVERT(sma llint, aDate) / 100))+ "/1/" + convert(char(2),convert(smallint,((CONVERT(smallin t, aDate) * .01) - (CONVERT(smallint, aDate) / 100)) * 100)))
FROM test
------------
elsie at 6/30/00 10:08:18 AM
what i am trying to do is change data in a column that has four or three characters to date e.g 199 to 1/99 or 0400 to 4/00
I got syntax error converting datetime from character string.
i must be doing something wrong
declare
@date1 datetime,
@date2 varchar(12)
select @date1= Effect_Code_Change from citynews
set @date1 = '15/00'
select @date2 = convert(varchar(12),@date1,101)
select substring(@date2,4,5)