Post reply

How convert UNICODE integer to date

  • September 5, 2006 at 8:47 am

    #91778

    Hi everybody!

    I have a column Fecha_Apertura int 4 that capture the data in format UNICODE. So i want convert this data to date. Someone know of any script? 

    Thanks by your help.

  • September 5, 2006 at 9:54 am

    #658576

    Unicode is a character format, not an integer format.  SQL Server stores unicode character data using the nchar and nvarchar types.

    Can you provide a few examples of what this data looks like? 

    Take a look at the CONVERT() function in BOL. 

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • September 5, 2006 at 10:19 am

    #658582

    Eddie is right Unicode doesnot affect DateTime field. If you want date to be displayed in different format use Conver function

    Thanks

    Sreejith

  • September 5, 2006 at 10:53 am

    #658597

    The column Fecha_Apertura int 4

    The data that I want convert is e.g:     1157472771

    And when I use the script: Convert (Fecha_Apertura as datetime), show the follow error:

    Arithmetic overflow error converting expression to data type datetime

    Thanks by your help

  • September 5, 2006 at 11:10 am

    #658605

    Can you tell me what "1157472771" should translate to? Cos u cannot convert that to date.

    Thanks

    Sreejith

  • September 5, 2006 at 11:23 am

    #658610

    It is simple first find the meaning of this Fecha_Apertura in English and then what is the formatte of this 1157472771 and it becames a Convert code to get the result.  Hope this helps.

    Kind regards,

    Gift Peddie

    Kind regards,
    Gift Peddie

  • September 5, 2006 at 2:50 pm

    #658646

    Thanks by your help...

  • September 6, 2006 at 12:58 am

    #658707

    Heh, looks very familiar. I once had to convert data from an application which stored dates as the number of seconds since January 1, 1970.

    Leif

  • September 6, 2006 at 9:36 am

    #658778

    Well spotted Leifah.

    SELECT DATEADD(ss, 1157472771, '1 jan 1970') gives 2006-09-05 16:12:51.000

    David.

     

  • September 6, 2006 at 9:54 am

    #658786

    You absolutely can convert dates and integers, but it's important to know how the system from which you are converting treats its date of origin.  For SQL Server, that's in 1900, for a lot of systems it's earlier back.  To show how I know the SQL date of origin, just do this (note in the second example, you can use fractions of days):

    SELECT CAST(1 AS DateTime)

    --1900-01-02 00:00:00.000

    SELECT

    CAST(1.5 AS DateTime)

    --1900-01-02 12:00:00.000

    So, typically, you have to know *how* the date is being stored (is it in days or seconds or what) and also what offset to use between the two systems.... You get scr*wed in SQL if you have to do a DATEDIFF to go back further than the beginning of the Gregorian calendar btw.

    So, let's assume that the system from which this is being translated stores in seconds.  Now we can probably divide to get an appropriate CAST-able date, but we also have to add the offset if the other system works on a different date of origin.  Let's assume that the other system uses the first day of 1800, it will be something like this:

    SELECT

    CAST((1157472771/(24*60*60)) + DATEDIFF(day,'1900/01/02','1800/01/01') AS DateTime)

    --1836-09-04 00:00:00.000

    This date is pretty early, indicating that it's possible the date of origin for the other system is later.  If it is 1970, as Leif suggested, you're going to get a reasonable date out of that number -- but you need to verify this.

    >L<

     

     

     

  • September 6, 2006 at 12:20 pm

    #658846

    Thanks...the problem has been resolved.

  • September 6, 2006 at 7:31 pm

    #658934

    Leifa and David hit the nail on the head... well done.

    --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)
    Intro to Tally Tables and Functions

Viewing 12 posts - 1 through 11 (of 11 total)

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