Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Unanswered: Date from Sybase Issues

    Hello everyone!

    I am having a rough issue here. I have a Sybase database for which I need to run a query on to select data based on a column named "regdate" which has a datatype of "DATE".

    One particular row has a value in the row of this column of "8/2/1999".

    Whenever I run a:

    select * from daily where regdate = '8/2/1999'

    It says:

    Restricted data type violation: Cannot convert 8/2/1999 to a timestamp.

    What would be the best way to go about working around this? This is not my database so I can't modify the tables in any way.

    Sorry if this is a newbie question....I have tried SQL CONVERT examples but I haven't gotten anywhere..

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by mikedcmdva View Post
    Hello everyone!

    I am having a rough issue here. I have a Sybase database for which I need to run a query on to select data based on a column named "regdate" which has a datatype of "DATE".

    One particular row has a value in the row of this column of "8/2/1999".

    Whenever I run a:

    select * from daily where regdate = '8/2/1999'

    It says:

    Restricted data type violation: Cannot convert 8/2/1999 to a timestamp.

    What would be the best way to go about working around this? This is not my database so I can't modify the tables in any way.

    Sorry if this is a newbie question....I have tried SQL CONVERT examples but I haven't gotten anywhere..
    Maybe your session has a different dateformat. Your server looks like dmy (or mdy) and your client is probably something like ymd.
    Try this:
    set dateformat dmy
    go
    select * from daily where regdate = '8/2/1999'
    go
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Feb 2012
    Location
    Pune
    Posts
    11
    convert in same format

    select * from daily where convert(char(8),regdate,101) = convert(char(8), '8/2/1999',101)

    or
    select * from daily
    where datediff(dd,regdate,'convert(date,8/2/1999')) =0

Posting Permissions

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