How convert UNICODE integer to date

Question

How convert UNICODE integer to date

Lisset

SSCommitted

Points: 1858
More actions
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.

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

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

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

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

Thanks

Sreejith

Lisset SSCommitted Points: 1858 More actions · Answer 3

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 4

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

Thanks

Sreejith

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

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

Lisset SSCommitted Points: 1858 More actions · Answer 6

Lisset

SSCommitted

Points: 1858

September 5, 2006 at 2:50 pm

#658646

Thanks by your help...

leifah Right there with Babe Points: 724 More actions · Answer 7

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

David McKinney SSChampion Points: 10472 More actions · Answer 8

Well spotted Leifah.

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

David.

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 9

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<

Lisset SSCommitted Points: 1858 More actions · Answer 10

Lisset

SSCommitted

Points: 1858

September 6, 2006 at 12:20 pm

#658846

Thanks...the problem has been resolved.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 11

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)