Skip to main content

CONVERT function

  • March 1, 2017
  • 2 replies
  • 16 views
Slaagh
Forum|alt.badge.img+1
  • Slaagh
  • Participating Frequently

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()))));

    2 replies

    Slaagh
    Forum|alt.badge.img+1
    • Slaagh
    • Author
    • Participating Frequently
    • March 1, 2017

    Can someone please answer this post?

    DerekW
    Forum|alt.badge.img
    • DerekW
    • Participating Frequently
    • March 6, 2017

    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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement