Results 1 to 4 of 4

Thread: Date Help

  1. #1
    elsie Guest

    Date Help

    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)

  2. #2
    Nathan Guest

    Date Help (reply)

    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)

  3. #3
    babreu Guest

    Date Help (reply)

    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)

  4. #4
    babreu (oops) Guest

    Date Help (reply)

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •