Post reply

I could sure use a "DATE" function and/or data type....

  • September 11, 2007 at 4:09 pm

    #176576

    I am migrating another SybaseSQLA db to SS2K5. 

    SybaseSQLA  has a DATE datatype which is effectively formatted "yyyy-mm-dd " or "yyyy-mm-dd 00:00:00.000000" thru  "yyyy-mm-dd 24:00:00.000000" It also has a conversion function DATE() which converts a datetime type value to DATE type 

    Select getdate()  , DATE(getdate())  produces:

    '2007-09-11 14:46:42.632000'  ,  '2007-09-11'

    What I am looking for in the new SS2K5 db is:

    1. A function like DATE()   User defined, yes?  If so does anyone have a version I can use. 

    2. A user defined data type like DATE

    If the new function or type simply defines the day start ('2007-09-11 00:00:00.000000') rather than inferring the entire day, I could live woth it ... although I must say that use of SybaseSQLA DATE types is handy.  For example:

    @date1 between DATE('2006-12-01 14:46:42.632000') and DATE('2006-12-31 14:46:42.632000')

    asks if @date1 is >= '2006-12-01 00:00:00.000000'  and <= '12-31 24:00:00.000000'

    This seems to me a very typical requirement for db users and developers.  Why isn't SS2K5 making this possible?

    Takauma

  • September 11, 2007 at 4:17 pm

    #732933

    There's a lot of discussion on the best way to do this. My understanding is that if you're feeling lazy, do this:

    CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 101))

    However the better performing method is:

    DATEADD(dd, CONVERT(FLOAT, GETDATE()), 0)

  • September 11, 2007 at 5:03 pm

    #732938

    Those work for me, Aaron.

    I extended both versions to the following in order to determine both the Day's beginning and end datetimes:

    select CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 101)) as 'DayBegin'

    , DATEADD

    (ms,-2,CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 101))) as 'DayEnd'

    select

    (DATEADD(dd, CONVERT(FLOAT, GETDATE()), 0)) as 'DayBegin'

    , DATEADD

    (ms,-2,(DATEADD(dd, CONVERT(FLOAT, GETDATE()), 1))) as 'DayEnd'

    maybe you have a slicker way to accomplish this?

    Joel

    Takauma

  • September 11, 2007 at 9:02 pm

    #732986

    There is no time such as 24:00:00.000 because that would represent the next day

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 11, 2007 at 9:09 pm

    #732987

    Subtracting 2 milliseconds from the date won't do it because dates have a 3.3 millisecond resolution. 

    And, instead of fooling around with the time for end date, why don't you just do the classic...

     SELECT *
    FROM sometable
  WHERE somedatecolumn >= @StartDate
     AND somedatecolumn < @EndDate + 1

    The way you're doing it will destroy any chance of getting an INDEX SEEK out of the query if the calculations are in the WHERE clause...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply