Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    7

    Unanswered: sybase not recognising 'DATE' data type

    Hi,

    I have used a query which has convert(date, colName) but it gives error:

    Can't find type 'date'

    The wierd thing is that same query runs on the other sybase instance.

    I have checked the version of both the sybase server instance:

    Adaptive Server Enterprise/12.5.3/EBF 13325 ESD#7/P/Sun_svr4/OS 5.8/ase1253/1951/64-bit/FBO/Fri Mar 24 11:00:22 2006

    any ideas why one of the sybase server not recognising date as data type?

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    If this server was upgraded recently and you had a UDT called date prior the upgrade, then the upgrade process skips installing the actual date.

    Compare the systypes entry with the server which is working and the one which is not.

  3. #3
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Also could be an issue with the upgrade process (for eg., installmaster not properly run) if it was indeed upgraded.

  4. #4
    Join Date
    Nov 2007
    Posts
    7
    thanks.

    I checked the systypes, and the date is appearing in both the dataserver, the only difference being the name is 'Date' (type column has value 56), where it is not working, while the name is 'date' (type column has value 49).

    I dont know if this makes any difference.

  5. #5
    Join Date
    Nov 2007
    Posts
    7
    is there any way I can extract the date part from datetime field, if i am unable to use the convert function?

  6. #6
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by hamidvirani
    thanks.

    I checked the systypes, and the date is appearing in both the dataserver, the only difference being the name is 'Date' (type column has value 56), where it is not working, while the name is 'date' (type column has value 49).

    I dont know if this makes any difference.
    49 is the one for the Sybase System Type. So, it looks like someone added a user defined type with sp_addtype.

    You need to first figure out if any of the objects are using the UDT date. If so, the best option is to rename it.

    If none of the objects are using the UDT date, then you can drop it.

    Once you clean up the UDT date, you can re-install it thro installmaster on a locked environment OR use a special proc called sp_twelve_five_one "add"

    Suggest better cleaning up the UDT, or you will have other problems. Think Sybase, should have forced this during an upgrade, i.e. not to allow upgrade if theres a UDT called date or time. Not sure if they have fixed it.

    Hope this helps.

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by hamidvirani
    is there any way I can extract the date part from datetime field, if i am unable to use the convert(date, function?
    to truncate the time portion
    select dateadd(dd,datediff(dd,'20000101',getdate()), '20000101')
    else
    select convert(char(8),getdate(),112)

  8. #8
    Join Date
    Nov 2007
    Posts
    7
    thanks everyone, that was helpful, I am using the convert as suggested by pdreyer, and that solved my problem

  9. #9
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by hamidvirani
    thanks everyone, that was helpful, I am using the convert as suggested by pdreyer, and that solved my problem
    Just remember, it may have solved the current problem, but you dont have a system datatype called DATE in your server. So, you will get into other problems if you dont fix that. If you are not the DBA of the environment, talk to the DBA and maybe he should be able to help you.

Posting Permissions

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