-1

I have a problem. I have a field START_VALUE where the data type is decimal. I want to convert Decimal to Date.

The data type START_VALUE is Decimal.The data of START_VALUE is :

START_VALUE |=========| | 240368 | | 198448 | | 197396 | | 126743 |

I want to convert to Datetime and what i want to expect is like 'yyyy-mm-dd 00:00:00.000'

I don't know if the value is Julian date or else. Please help me to find the right select query. Thanks.

cindy
  • 35
  • 1
  • 3
  • 7
  • 2
    What database are you using? What is the type of the column? What date do you expect for the values you posted in your question? Without that it could be anything.. for example do you want "24 March 1968" for the first row? – Erwin Bolwidt Jun 20 '14 at 08:16
  • Use `from_unixtime(START_VALUE) as mydate` if you are using mysql. – Think Different Jun 20 '14 at 08:18
  • I use SQL server database, source type of the column is decimal and i want convert to date. and the value that i expect is like 'yyyy-mm-dd 00:00:00.000'. because i want migration from SSIS to datastage. – cindy Jun 20 '14 at 08:31
  • sorry.. i must try in oracle, not sql server. my fault. Please help me. – cindy Jun 20 '14 at 09:43
  • This is almost certainly not Unix time. However, if you don't know what the base-line date for this number is, or how it's constructed, I'm unsure how you can expect others to help. You need to talk to the person responsible for the data/database. – Ben Jun 20 '14 at 10:32

2 Answers2

1

Are you looking for this

CREATE FUNCTION Number_To_Date(No_Of_Days NUMBER)
RETURN DATE IS
BEGIN
  RETURN to_date('1899-12-30', 'YYYY-MM-DD') + No_Of_Days;
END;
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
0

You can use the following query in SQL Server to parse it to datetime

SELECT CONVERT(DATETIME, SUBSTRING(CAST(START_VALUE
 as varchar(8)),1,2) + '-' + SUBSTRING(CAST(START_VALUE
 as varchar(8)),3,2) + '-' + SUBSTRING(CAST(START_VALUE
 as varchar(8)),5,2),5) AS DateTimeValue

FROM Table

If you are using Oracle then you can use the following query

SELECT TO_DATE(CAST(START_VALUE AS VARCHAR(8), 'DDMMYY') FROM Table
Kiran Hegde
  • 3,651
  • 1
  • 16
  • 14