Searching dates in table and only return when fields are not null

Question

1.00/5 (1 vote)

See more:

Hi

I wonder is someone could quickly help me out put some SQL query together. I have some data that i would like to return back into a report, selecting some fields from the database table.

Fields as follows:

VERSION_ID_FK
DATE_TYPE
FROM_DAY
FROM_MONTH
FROM_YEAR
TO_DAY
TO_MONTH
TO_YEAR

So basically what i want to get out of this is that the client has got a way in which they enter dates in system and sometimes they only just enter the year without the day and month or they only enter month and year without the day.

So i want the sql please if someone can help to check first is there a day then return either or both FROM_TO, FROM_MONTH, FROM_YEAR, TO_DAY, TO_MONTH, To_YEAR if these fields are not null. These fields are linked bu the Version_ID_FK.

Then next check would be if FROM_DAY or TO_DAY is blank return FROM_MONTH, FROM_YEAR TO_MONTH, TO_YEAR. and if both day and month are null just return the year for either both or just one of them depending which one has got data in them.

I hope that is clear and hope someone will be able to help me out quickly.

Thanks in advance. Let me know if you have any questions

So here is the current SQL that someone had already put in place, but i have now added the fields mentioned above to the table

SQL

select p.prod_date_id, p.version_id_fk, p.DATE_TYPE_ID, p.date_type, p.date_type, p.date_from, p.date_to, p.circa, p.notes, p.created_by, p.created_date, p.modified_by, p.modified_date from
mdd_version_production_dates p, mdd_versions v
where v.version_id = p.version_id_fk
and v.version_id1 = (case when :VERSIONID1 is null then v.version_id1 else :VERSIONID1 end)

Posted 4-Nov-14 1:40am

Geofferz

Updated 4-Nov-14 2:05am

v2

Add a Solution

Comments

DamithSL 4-Nov-14 7:45am

what you have tried so far? can you update the question with that sql query?

Maciej Los 4-Nov-14 7:45am

What version of MS SQL server?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-11-04T02:22:00

Solution 1

Try this:

SQL

SELECT <List_Field>
FROM <TableName>
WHERE NOT DateField IS NULL

or use ISNULL[^] or COALESCE[^] function to replace NULL with default new value.

SQL

SELECT COALESCE(NULL, '1899-01-01') AS DefaultNewDate

Posted 4-Nov-14 2:22am

Maciej Los

Updated 4-Nov-14 2:25am

v2

Comments

Shweta N Mishra 4-Nov-14 9:32am

+5 :)

Maciej Los 4-Nov-14 9:33am

Thank you ;)