int to timestamp conversion

Database
Enthusiast

int to timestamp conversion

please need help with conversion below:

SELECT ExpDate=convert(DateTime,convert(varchar(100),(a.OfferDateINT),112))+1

FROM a

WHERE convert(int,convert(varchar(100),(GETDATE()),112))=convert(int,convert(varchar(100),(a.UpdateDate),112))

 

a.OfferDateINT,a.UpdateDate is integer columns

 

Tnx a lot!

3 REPLIES
Senior Apprentice

Re: int to timestamp conversion

Hi,

 

I find this difficult to help you with because you haven't really described the logic. What is this meant to be doing?

 

What does "ExpDate=convert(DateTime,convert(varchar(100),(a.OfferDateINT),112))+1" achieve?

To me this would return a 'True' or 'False' answer (i.e. boolean) but Teradata doesn't have a Boolean data type.

 

You say that "a.OfferDateINT,a.UpdateDate is integer columns" which is useful information, but can you provide examples of the numeric values that they would contain for certain dates.

 

For instance, what integer value would one of them contain for 27 August 2016?

 

Below are a number of assumptions, telling me if those assumptions are correct or not will help:

  1. "GETDATE()" returns the current date. Replace that with DATE or CURRENT_DATE (either will do).
  2. "convert(DateTime,convert(varchar(100),(a.OfferDateINT),112))+1" converts the content of "a.OfferDateINT" to a date value and then adds 1 day to it
  3. "convert(int,convert(varchar(100),(GETDATE()),112))=convert(int,convert(varchar(100),(a.UpdateDate),112))" only selects rows where the content of "a.UpdateDate" is today's date (in whatever format).

 

Some more information would help.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: int to timestamp conversion

Hi Dave,

"ExpDate=..." is proprietary T-SQL syntax assign a column alias (but "... AS ExpDate" is also supported), your other assumptions are right.

 

The best way to convert YYYYMMDD to a Date is 

Cast(OfferDateINT - 19000000 AS DATE)

and back

(Cast(UpdateDate AS INT) + 19000000)

The WHERE-condition is quite stupid as it's converting UpdateDate to a string and then back to the same INT-date.

SELECT Cast(OfferDateINT - 19000000 AS DATE)+1
FROM a
WHERE (Cast(Current_Date AS INT) + 19000000) = UpdateDate

 

 

 

 

Enthusiast

Re: int to timestamp conversion

Hi all!

 

First of all - tnx a lot!

 

You clearly understood the logic.

 

At the end i need convert integer column like '20170501' to timestamp and that's what SQL do.

 

In Teradata it's more difficult but i will try dnoeth  solution:)