CONVERT function

Slaagh

Hello,
I have the following WHERE clause in a SQL script that I am trying to turn into a Vertica view. It is my understanding that CONVERT does not exists in Vertica. Does anyone know how to do this in Vertica? I am basically looking for the first of January, 3 years ago.

WHERE DATETIMESTAMP >= CONVERT(DATETIME,'01-01-'+CONVERT(VARCHAR(5),YEAR(DATEADD(YYYY,-3,GETDATE()))));

Slaagh

Can someone please answer this post?

DerekW

Hi Slaagh,

First of all, my advice to you if you want people to help you, is to be a bit more patient not too pushy. Not everybody is looking at this forum on a daily or hourly basis.

That said, you could do it on several kind of ways using casting and standard date functions:
https://my.vertica.com/docs/8.0.x/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Operators/DataTypeCoercionOperatorsCAST.htm?TocPath=SQL%20Reference%20Manual|SQL%20Language%20Elements|Operators|Data%20Type%20Coercion%20Operators%20(CAST)|_____0

https://my.vertica.com/docs/8.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Date-Time/DateTimeFunctions-1.htm?TocPath=SQL%20Reference%20Manual|SQL%20Functions|Date%2FTime%20Functions|_____0

Examples:

WHERE datetimestamp >= '2016-01-03'::timestamp
or
WHERE datetimestamp >= (add_months(date_trunc('year',current_date),-36) + 2)::timestamp
or
WHERE datetimestamp >= CAST(add_months(date_trunc('year',current_date),-36) + 2 as timestamp)

Cheers,
Derek