2

i have an integer field which has date values but i would like to convert it as date field. I have tried several methods but with no success. The field has Date values but is stored as an Integer. This is what i have tried:

  cast(MYFIELD AS DATE) AS MYCOLUMN

but i get this error "Cannot cast type INT4 to DATE".
I have done several research but coul not find good solution for netezza.

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
moe
  • 5,149
  • 38
  • 130
  • 197
  • what does your integer value for date look like ? – BWS Dec 10 '13 at 20:13
  • this is how it look like: 201004 – moe Dec 10 '13 at 20:20
  • how would one know how to convert that number to a date? Is there some standard to be followed? – BWS Dec 10 '13 at 20:35
  • this data is being used in some other places so the data type has to be a date format in order to use for some other purpose – moe Dec 10 '13 at 20:38
  • how do you translate "201004" into a date? what's the YEAR, MONTH and DAY? Once you have that, you can convert it into a date format that SQL will be able to use. – BWS Dec 10 '13 at 20:41

2 Answers2

1

I don't think this is something that you can do, not in an obvious way at least. There are hundreds of ways a human could represent a date as an int, so the conversion would not be built in.an int would be something like 20120415 or 04152013 or hundreds of other formats and a date would be something like '2012-04-15' I suggest you look at the top answer for How i can get the first 3 digits in 123456 Numbers in sql? and extract your data manually. what you should do though, is convert the field into a real date field and edit the dependencies to expect that format.

Community
  • 1
  • 1
Mike DeRoy
  • 133
  • 7
1

You can concatenate 01 and then run a to_date 

select to_date(201004||'01','YYYYMMDD')
Niederee
  • 4,155
  • 25
  • 38