CODEWITHSUNDEEP
sqldatetype-conversionnumericvertica
FightMilk
FightMilk

Reputation: 174

SQL: Changing numeric date to "Date"

My date variables are Numeric(37) in the format (20160404,20160405,...). Following other questions I have tried the following,

select convert(datetime, date_var)

with error Column datetime does not exist. 

select convert(date, date_var)

same error as above. Also tried,

select convert(date, convert(float, date_var))

with the same error.

select cast(convert(VARCHAR, date_var) as datetime)

gives a similar error Column "VARCHAR" does not exist

I apologize, my knowledge of SQL is just a few weeks old and if I needed to do further research before posting this question. I feel I am making some error, such as using wrong function for a wrong database. Can anyone please help me?

Upvotes: 0

Views: 4946

Answers (3)

marcothesane
marcothesane

Reputation: 6749

You mean this, I suppose:

WITH
input(datenum) AS (
          SELECT 20180602
UNION ALL SELECT 20180603
)   
SELECT datenum::VARCHAR(16)::DATE FROM input;

Output is:

datenum
2018-06-02
2018-06-03

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

I would just use cast():

select convert(date_var as datetime)

I'm not sure if Vertica supports the particular conversion, but this should work:

select convert(date_var as varchar(255))

cast() is the ANSI standard function for converting between different types.

In Vertica, you can also do:

select date_var::varchar(255)

This is a syntax inherited from Postgres.

For your actual problem, I wouldn't be surprised if you had to convert twice:

select (date_var::varchar(255))::datetime

Upvotes: 1

bitsNbytes
bitsNbytes

Reputation: 64

it depends what you are using to run this statement. i guess an easy outside the box approach would be to run a script. like the one bellow.

For the conversion of numeric value to character value try the following code

PROC SQL;

  CREATE TABLE y AS

  SELECT PUT(x.subjid, 6.) AS subjid,

         PUT(x.date, YYMMDD10. ) AS date

  FROM x;

QUIT;

or if you need to do a select statement only..

SELECT CONVERT(DATETIME,CONVERT(VARCHAR(8),NumberDate),112)

or if u need to declare a new numeric and set the date format of x

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), @x));

Upvotes: 0

Related Questions