Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: datetime fields and constarints

    I've created a user defined datatype called t_date. It's a datetime datatype. I would like to bind a rule to the udt that constrains the time to 12:00am. Can anyone recommend the best way to do this.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about this?


    create rule [myrule] as @date=convert(varchar(10),@date,101)+' 12:00'
    GO

  3. #3
    Join Date
    Sep 2003
    Posts
    364

    Talking

    Yep that works, in fact that's what I've been doing. I was hoping there was a more efficient way to check instead of having to cast to a string and back to a date again. It's not in your example but I cast back to a datetime to do the compare. Not sure if I have to or not.

    Do you know how costly casting data in SQL server is?

    Thanks for the help :-)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It doesn't matter how costly it is, 'cause its the only way! You wants it, you pays for it!

    blindman

  5. #5
    Join Date
    Sep 2003
    Posts
    364
    Thanx blindman, damn good thing my company has deep pockets because weez gonna have to pay

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pay for processors...pay for memory...make sure they pay you good too!

    Seriously, casting/converting datetime and string values is probably not a fast process, but for the string lengths you are dealing with it should not have a big impact on performance. And there is no way around it. MS Access, MS Excel, and other Microsquash products allow you to take the integer portion of the datetime value (a vast operation), but SQL Server uses a different logic for storing datetime values and this method won't work.

    blindman

Posting Permissions

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