CODEWITHSUNDEEP
sqlpostgresql
cpburke94
cpburke94

Reputation: 1277

How to pull a count of cells with a missing date in a YYYY-MM-DD format?

I want to pull a count of cells that are missing the day and only have the month and year in the cell. I'm in pgAdmin and using PostGreSQL on a Mac.

This is the format of the date if everything is entered:

2010-01-01

So far, I have the following query started, with the date format just filled in for context:

SELECT COUNT(nct_id)
FROM studies
WHERE start_date LIKE '2010-01-NULL'

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I am guessing that start_date is a string, not a date. You really should store dates as dates, not string.

But, you can do what you want using regular expressions:

where not (start_date ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')

Upvotes: 1

Related Questions