1

I have a column saved as a character data type. This column is what I am going to be using as a date. The column goes "YYYY-MM-DD" in that format.

This is a problem because if I ever need to filter by date, I have to go

select col_1, col_2
from table
where date LIKE '2016-04%;

If I want to search for a date range, this turns into a giant complicated mess.

What is the easiest way to convert this to a "date" data type? I want it to continue to be in YYYY-MM-DD order (no timestamp).

My ultimate goal is to be able to search for dates in a format like this:

select col_1, col_2
from table
where date between 2016-01-01 AND 2016-05-31;

What do you guys recommend? I am terrified I am going to corrupt my date if I use an alter statement to convert my data type. (I have a copy of the data saved and can upload it again, but it will take forever.)

Edit: This is a VERY Large table.

Edit Part 2: I originally stored the data as a varchar data type because my dates were not uploading correctly and I got an error message when I tried to save as a date data type. The every date in this column is in the "YYYY-MM-DD" order. My solution was to save it as varchar to avoid the error message (I couldn't figure out what was wrong. I even got rid of leading and trailing spaces.)

Improve this question
3
  • 1
    "because my dates were not uploading correctly and I got an error message when I tried to save as a date data type" that should have been a fair warning right at the start. One reason to use the correct data type is to avoid storing invalid data (e.g. 2016-16-00 or 2015-02-29) Commented Mar 31, 2016 at 11:53
  • None of the dates have a weird format like that, my only guess is making there were trailing blank spaces. Commented Mar 31, 2016 at 11:56
  • Leading or trailing whitespace would not prevent a conversion to a date. select ' 2016-01-01 '::date works perfectly fine Commented Mar 31, 2016 at 11:57

3 Answers 3

Reset to default
5

Storing a date as a varchar was the wrong choice to begin with. It's very good that you want to change that.

The first step is to convert the columns using an ALTER TABLE statement:

alter table the_table
   ALTER COLUMN col_1 TYPE date using col_1::date, 
   ALTER COLUMN col_2 TYPE date using col_2::date;

Note that this will fail if you have any value in those columns that cannot be convert to a correct date. If you get that you need to first fix those invalid strings before you can change the data type.

I want it to continue to be in YYYY-MM-DD order

This is a misconception. A DATE (or timestamp) does not have a "format". Once it's stored as a date you can display it in any format you want.

My ultimate goal is to be able to search for dates in a format like this:

2016-01-01 is not a valid date literal, a proper (i.e. correctly typed) date constant can be specified e.g. using date '2016-01-01' (note the single quotes!

So your query becomes:

select col_1, col_2
from table
where col_1 between date '2016-01-01' AND date '2016-05-31';

If you have a lot of queries like that you should consider creating an index on the date columns.

Regarding the date constant format:

Are you telling me that despite having the varchar data types, I can still (as of right now) search between specific dates by just typing the word date and putting single quotes between two dates

No, that's not the case. SQL is a strongly typed language and as such will only compare values of the same type.

Using an ANSI date literal (or e.g. to_date()) results in a type constant (i.e. a value with a specific data type).

The difference between date '2016-01-01' and '2016-01-01' is the same as between42(a number) and'42'` (a string).

If you compare a string with a date, you are comparing apples and oranges and the database will do an implicit data type conversion from one type to the other. This is something that should be avoided at all costs.

If you do not want to change the table, you should use the query sagi provided which explicitly converts the strings to dates and then does the comparison on (real) date values (not strings)

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

1) If I do get an error message when I try to alter the table because of my "YYYY-MM-DD" order, how would you suggest that I change the order in order to make it work?
2) Are you telling me that despite having the varchar data types, I can still (as of right now) search between specific dates by just typing the word date and putting single quotes between two dates? I don't have to use the alter table statement on the table (worst case scenario), also, I definitely plan on using an index on the date column.
@LunchBox: 1) find the rows that have invalid dates e.g. by using a regex then change those values.
When I am changing the values, is there a specific way I should change the values? I.e. go and alter the column so that the values look like this 'YYYYMMDD'?
Note that to_date(date_col,'yyyy-mm-dd') will also fail if there are invalid values in the column.
|
1

You can use POSTGRES TO_DATE() cast function :

SELECT col_1,col_2
FROM Your_Table
WHERE to_date(date_col,'yyyy-mm-dd') between to_date('2016-05-31','yyyy-mm-dd') and to_date('2016-01-01','yyyy-mm-dd')
Improve this answer

Comments

0

What @a_horse said.

Plus, if you can't change the data type for some odd reason, to_date() is a safe option to convert the column on the column, but there is no point to use the same expression for provided constants. So:

SELECT col_1, col_2
FROM   tbl
WHERE  to_date(date, 'YYYY-DD-MM') BETWEEN date '2016-05-31' AND date '2016-01-01';

Or just use string literals without type. The type date is deferred from the context in this expression. And you don't even need to_date(). Since you are using ISO format already. A plain cast is safe:

WHERE date::date BETWEEN '2016-05-31' AND '2016-01-01';

Be sure to use ISO 8601 format for all date strings, so they are unambiguous and valid with any locale.

You can even have an expression index to support the query. Match the actual expression used in queries:

CREATE INDEX tbl_date_idx ON tbl ((date::date));  -- parentheses required!

But I wouldn't use the basic type name date as identifier to begin with.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.