Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Posts
    74

    Unanswered: DatePart function in ANSI SQL

    Hi folks,

    How can I re-write the following code in ANSI SQL code:

    select cast(datepart(month, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/' +
    cast(datepart(day, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/'+ cast(datepart(year, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar), event_instance_id, max(time_stamp)
    from usmuser.usm_sli_event_data
    where event_instance_id=10019
    group by cast(datepart(month, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/' +
    cast(datepart(day, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/'+
    cast(datepart(year, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar),
    event_instance_id
    order by event_instance_id

    Thanks for your help!
    -Parul

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please explain why?

    also, for those of us not patient enough to unravel the intricacies of this delectable code fragment, would you kindly please explain what it's doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2002
    Posts
    74
    The code should be portable so it can used be used on other databases as well, not just SQL Server.
    Basically, the time_stamp field has number of seconds since 1/1/1970 and the datepart function is calculating the month, day, and year. The goal is to get the last time_stamp per event_instance_id per day.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    unfortunately, your quest will be unsatisfied

    date functions are among the more un-robust of the ANSI SQL capabilities

    there is practically no hope that you will get exactly the same code to run "on other databases as well, not just SQL Server"

    even if we did manage to figure out a way to do what you're doing with ANSI SQL functions (and good luck to you, as i'm going to pass), it probably wouldn't run on SQL Server to start with
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    This is what data abstraction layers are for...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Oct 2002
    Posts
    74
    Thanks r937!

Posting Permissions

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