Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2004
    Posts
    91

    Unanswered: Populating database with timestamps...

    Hi Everyone....

    Crazy one here....

    I need to populate a table with all the times that
    are available in a 24 hour period, down to the 5 minute
    interval.

    So the table should look like....

    id ds (datetime stamp)
    --- --------------------------
    0 1/1/2005 00:00:00
    1 1/1/2005 00:05:00
    2 1/1/2005 00:10:00
    3 1/1/2005 00:15:00
    .........
    xx 1/1/2005 23:55:00



    Please advise on a way to accomplish this in a script....

    thanks
    tony

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create a table of sequential numbers from 0 through (24*12)=288. Then use:
    dateadd(minute, 5*[sequencenumber], [StartTime]) to create a list of time intervals.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2004
    Posts
    91

    update...

    This is what i got going in the MS SQL Side...


    truncate table tblifbatchtimes
    go

    BEGIN
    DECLARE @st as datetime
    select @st = '1/1/2005 00:00:01 AM'
    insert into tblifbatchtimes
    (
    IFBAT_dtBatchTime, IFBAT_lChangeCTR, IFBAT_boolSelectedFlag
    )
    values
    (
    @st,
    0,
    0
    )
    select @st = '1/1/2005 00:05:00 AM'
    while (@st < '1/2/2005')
    BEGIN
    insert into tblifbatchtimes
    (
    IFBAT_dtBatchTime, IFBAT_lChangeCTR, IFBAT_boolSelectedFlag
    )
    values
    (
    @st,
    0,
    0
    )
    select @st = dateadd(minute, 5, @st)
    END
    END
    go

    delete from tblifbatchtimes where ifbat_dtBatchtime = '1/2/2005'
    go



    Works great....


    but when i try to move it over to the Sybase ASA system -
    it fails (of course)

    can anyone show me why the MS SQL works and the Sybase fails???

    thanks
    tony

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not sure I would if I could, 'cause I wouldn't want to help you continue down the wrong path.

    DON'T use permanent tables to store temporary data.
    DON'T hardcode dates into your code.
    DO use the method I already outlined, and which can create time intervals for any day (not just 1/1/2005) on the fly. I mean, do you really only need this dataset for 1/1/2005?

    My best guess on why it doesn't work in Sybase would be difference in the way dates are handles, but it has been five years since I worked with Sybase. Of course, from what I know of Sybase, they haven't actually changed or improved their product since then...

    What error are you getting in Sybase?
    And wait a minute....why are you developing in SQL Server just to port the code over to Sybase?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2004
    Posts
    91

    okey dokey - since you asked...

    >>Not sure I would if I could, 'cause I wouldn't want to help you continue
    >>down the wrong path.

    The path was layed down prior to my starting on it -
    i simply need to recreate the values in the table -
    originally they were in 15 minute increments, now the customer
    wants 5 minute intervals.


    >>DON'T use permanent tables to store temporary data.
    There is no temp tables here... the associated fields in the
    table are used to flag the times that the code is to be run.

    >>DON'T hardcode dates into your code.
    OK - refer to the first statement.

    >>DO use the method I already outlined, and which can create time intervals
    >>for any day (not just 1/1/2005) on the fly. I mean, do you really only need
    >>this dataset for 1/1/2005?
    The actual day part (1/1/2005) is disposed of on the business layer
    side of the code, - so the actual date doesn't matter.


    >>My best guess on why it doesn't work in Sybase would be difference in the
    >>way dates are handles, but it has been five years since I worked with
    >>Sybase. Of course, from what I know of Sybase, they haven't actually
    >>changed or improved their product since then...
    >>What error are you getting in Sybase?
    Sybase is dying at the declare statement...
    truncate table tblifbatchtimes
    go
    BEGIN
    DECLARE @st as datetime <----- error here
    select @st = '1/1/2005 00:00:01 AM'


    >>And wait a minute....why are you developing in SQL Server just to port the
    >>code over to Sybase?
    Excellent question young skywalker - we are moving to an open backend
    database direction... kinda.... sybase, sql server, and oracle are supported,
    but i haven't gotten to the oracle hump yet...

    And as a side note - the java front end that sybase forces me to use
    is completely crap-o-la...


    Thanks for the reply!!

    take care
    tony

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    if object_id('dbo.fn_5minuteDayView') is not null
       drop function dbo.fn_5minuteDayView
    go
    create function dbo.fn_5minuteDayView (
       @date datetime					 ) returns table
    as return (
       select top 100 percent tblHours.[Hours], tblMinutes.[Minutes],
    	  DateCharacter = convert(char(10), @date, 101) + ' ' +
    		 right('00'+cast(tblHours.[Hours] as varchar(5)), 2) + ':' +
    		 right('00'+cast(tblMinutes.[Minutes] as varchar(5)), 2) + ':00',
    	  DateValue = cast(convert(char(10), @date, 101) + ' ' +
    		 right('00'+cast(tblHours.[Hours] as varchar(5)), 2) + ':' +
    		 right('00'+cast(tblMinutes.[Minutes] as varchar(5)), 2) + ':00'
    		 as datetime) from (
    	  select [Hours]=a1.seq + a2.seq from (
    	  (
    		 select seq=0   union
    		 select 1	   union
    		 select 2	   union
    		 select 3	   union
    		 select 4	   union
    		 select 5	   union
    		 select 6	   union
    		 select 7	   union
    		 select 8	   union
    		 select 9	   union
    		 select 0
    	  ) a1
    	  cross join 
    	  (
    		 select seq=0   union
    		 select 10	  union 
    		 select 20			
    	  ) a2 )
    	  where (a1.seq + a2.seq) < 24
       ) tblHours
       cross join (
    	  select [Minutes]=5 * (a1.seq + a2.seq) from (
    	  (
    		 select seq=0	  union
    		 select 1		  union
    		 select 2		  union
    		 select 3		  union
    		 select 4		  union
    		 select 5		  union
    		 select 6		  union
    		 select 7		  union
    		 select 8		  union
    		 select 9
    	  ) a1
    	  cross join (
    		 select seq=0   union
    		 select 10
    	  ) a2 )
    	  where (a1.seq + a2.seq) < 12
       ) tblMinutes
       order by 1, 2
    )
    go
    declare @d datetime
    set @d = current_timestamp
    select * from dbo.fn_5minuteDayView (@d)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what an interesting challenge

    writing sql that will work in both sql server and sybase shouldn't be that difficult (it's like the difference between canadian english and british english) but writing sql that will also work in oracle (american english) is next to impossible, especially where date functions are concerned

    one thing that oracle will reject immediately is SELECT TOP 100 PERCENT

    that syntax always struck me as particularly ludicrous anyway

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Actually Horacle will reject SELECT without FROM even faster
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Due to the portability requirement, this sounds like a job for VB or one of its cousins to me!

    -PatP

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    VB doesn't have cousines, at best it has uncles that don't really like their nephew
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    VB doesn't have cousines, at best it has uncles that don't really like their nephew
    Ah, you've got to think on a grander scale. I see Delphi and PowerBuilder (among many others) as VB's cousins. From that perspective, VB is just one kid that makes a lot of noise tucked in with a hoard of others.

    -PatP

  12. #12
    Join Date
    Jan 2005
    Location
    Washington, DC
    Posts
    5
    Quote Originally Posted by alt-088
    >>And as a side note - the java front end that sybase forces me to use
    is completely crap-o-la...


    Thanks for the reply!!

    take care
    tony

    Tony;

    I assume the java front-end you are talking about is Sybase central for ASE 12.x? If you dig around, you can find the older sybase central (version 3.0.0.1887 or thereabouts) which was bundled with ASE 11.7+; use it instead. You can install it over your current and it will pick up all of your existing settings and ini file.

    Regards,
    Craig

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oracle's Java-based interface is also complete crap. Are there any good Java applications out there?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Pat Phelan
    Ah, you've got to think on a grander scale. I see Delphi and PowerBuilder (among many others) as VB's cousins. From that perspective, VB is just one kid that makes a lot of noise tucked in with a hoard of others.
    Delphi was a mature development tool before VB was even born, so, as I said, it has uncles at best. And "grander scale" is what I do
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Feb 2005
    Location
    Omaha, NE
    Posts
    83
    Sybase syntax for "declare" doesn't use the keyword "as"

    DECLARE @st datetime

    not

    DECLARE @st as datetime

Posting Permissions

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