CODEWITHSUNDEEP
sqlteradata
user3461502
user3461502

Reputation: 323

How to retrieve date in a different format - SQL

I have a column with date in this format: 2017-01-31 23:07:50.569000

In my query, I am using

select * 
from tableA
where progress_datetime between 20200319 and 20200324

However, this does not retrieve the list of rows between those days and gives an invalid error. Does anyone know how I can retrieve the correct date without having to input the entire time in hours, minutes and seconds?

I am using TERADATA.

Thank you.

Upvotes: 0

Views: 155

Answers (2)

Bohemian
Bohemian

Reputation: 425258

Cast the strings to a date type:

select * 
from tableA
where progress_datetime between date '2020-03-19' and date '2020-03-24'

By not modifying the data of progress_datetime, an index on that column wold be able to be used, and even if not, it will be much more efficient than having to calculate the result of a function on progress_datetime for every row in the table.

Upvotes: 1

Teja Goud Kandula
Teja Goud Kandula

Reputation: 1574

select * from tableA
where  
CAST(progress_datetime as DATE)
between '2020-03-19' and '2020-03-24'

The column progress_datetime looks like it is of type Timestamp(6). The above cast converts it into ANSI date format.

You can find more info on Extracting the date and time from this post. Similarly you can read this documentation.

Upvotes: 0

Related Questions