Dynamic date setting in loops

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • arny64
    New Member
    • Oct 2007
    • 4

    #1

    Dynamic date setting in loops

    Hello everybody,

    I've been trying to write a script to populate a table.
    One filed is of 'date' type and I would like to insert dates different from record to record.
    I thought about creating a loop and then try to increment the day (or the hour, I don'care) by using the loop index.
    There comes of course a problem of casting from integers to strings (or date).

    I tried to do something like:

    DECLARE @K INT
    WHILE(@K<10)
    BEGIN
    INSERT INTO TABLE1 (DATETIME) VALUES ('20071026 11:'||CAST(@K AS VARCHAR)||'00')
    SET @K=@K+1
    END

    but it didn't work ...

    Would you please suggest a method of performing this action?

    Thanks in advance,

    Stefano.
  • debasisdas
    Recognized Expert Expert
    • Dec 2006
    • 8119

    #2
    What is the data that you are trying to insert. I dont think the initial condition of the loop satisfies at all.

    Comment

    • arny64
      New Member
      • Oct 2007
      • 4

      #3
      The data type is 'datetime' and I was trying to build the string by a cat operation.
      For instance, to build '20071025 12:28:40' I coded:

      '20071025 12:'+cast(@j+28 , varchar)+':40'

      where @j is the loop variable.

      The aim is to obtain strings with dates like:

      ............... ............... ..........
      '20071025 12:29:40'
      '20071025 12:30:40'
      '20071025 12:31:40'
      '20071025 12:32:40'
      '20071025 12:33:40'

      and so on ....

      Comment

      • sayedul
        New Member
        • Oct 2007
        • 12

        #4
        If you have the table like below:
        TABLE1 ([DATETIME] DATETIME)


        The modification your script to correct one as follows:

        DECLARE @K INT
        SET @K = 0
        WHILE(@K<10)
        BEGIN
        INSERT INTO TABLE1 (DATETIME) VALUES (convert(dateti me,'2007-10-26 11:'+CAST(@K AS VARCHAR)+':00') )
        SET @K=@K+1
        END

        Comment

        • arny64
          New Member
          • Oct 2007
          • 4

          #5
          I implemented the suggested modification, the parser says ok, but the run.time execution got the following error:

          Server: Msg 242, Level 16, State 3, Line 12
          The conversion of a char data to a datetime data type resulted in
          an out-of-range datetime value.
          The statement has been terminated

          The troubles keep going ...



          Originally posted by sayedul
          If you have the table like below:
          TABLE1 ([DATETIME] DATETIME)


          The modification your script to correct one as follows:

          DECLARE @K INT
          SET @K = 0
          WHILE(@K<10)
          BEGIN
          INSERT INTO TABLE1 (DATETIME) VALUES (convert(dateti me,'2007-10-26 11:'+CAST(@K AS VARCHAR)+':00') )
          SET @K=@K+1
          END

          Comment

          • arny64
            New Member
            • Oct 2007
            • 4

            #6
            I already found my error! I wrote incorrect datetime format!
            Now everything works!

            Sorry, I am stupid ...

            Thanks a lot anyway for the helpful suggetsions!!!

            Stefano.

            Comment

            Working...