Date column with 1900-01-01 value

Question

Date column with 1900-01-01 value

gissah

SSCommitted

Points: 1815
More actions
December 23, 2014 at 7:10 am

#293850

I have a date column with 1900-01-01 value, I am trying to get the min(date) without 1900-01-01.My problem here is: there are some records with both 1900-01-01 and real date. How do I get the MIN of the real date without getting 1900-01-01. Below is my query sample. Any help will be appreciated.
CASE WHEN MIN(CAST(PROJECTEDSTARTDATE AS DATE)) = '1900-01-01' THEN NULL ELSE MIN(CAST(PROJECTEDSTARTDATE AS DATE))
END as 'ProjectStartDate'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you not use a WHERE clause to filter out the rows with the 1900-01-01 date?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gissah SSCommitted Points: 1815 More actions · Answer 2

Thanks that is what I ended up doing with a subquery.

select pt.projinvoiceprojid,MIN(CAST(pt.PROJECTEDSTARTDATE AS DATE)) AS 'SOPOrderDate_ProjectStartDate'

from projtable pt

where pt.PROJECTEDSTARTDATE !='1900-01-01'

group by pt.projinvoiceprojid

order by pt.projinvoiceprojid

ScottPletcher SSC Guru Points: 101120 More actions · Answer 3

gissah (12/23/2014)
Thanks that is what I ended up doing with a subquery.
select pt.projinvoiceprojid,MIN(CAST(pt.PROJECTEDSTARTDATE AS DATE)) AS 'SOPOrderDate_ProjectStartDate'
from projtable pt
where pt.PROJECTEDSTARTDATE !='1900-01-01'
group by pt.projinvoiceprojid
order by pt.projinvoiceprojid

Be careful! Do you really want to ignore / not list at all any projects where all pt.PROJECTEDSTARTDATEs are '19000101' or NULL (the code above will also exclude all NULL dates, if any).

To get the MIN date beyond 19000101, you could do this instead:

select pt.projinvoiceprojid,

MIN(CASE WHEN CAST(pt.PROJECTEDSTARTDATE AS DATE) >= '19000101' THEN CAST(pt.PROJECTEDSTARTDATE AS DATE) END) AS 'SOPOrderDate_ProjectStartDate'

--,...

from projtable pt

~~where pt.PROJECTEDSTARTDATE !='1900-01-01'~~

group by pt.projinvoiceprojid

order by pt.projinvoiceprojid

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".