Missing Century in Date

Question

Post reply

Missing Century in Date

Viewing 10 posts - 16 through 24 (of 24 total)

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

Sean Pearce SSCoach Points: 15999 More actions · Answer 1

SQLSeTTeR (7/31/2014)
Eirikur worked perfect. It did exactly what I needed it to do. Thanks again! 😀

What about 7/26/19?

declare @mycutoff date = '01/01/2020';

select [Date] = CONVERT(VARCHAR(10),CASE WHEN convert(datetime,'7/26/19',1) >= @mycutoff THEN DATEADD(YEAR,-100,convert(datetime,'7/26/19',1)) ELSE convert(datetime,'7/26/19',1) END,103)

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 2

I'm using:

DECLARE @CutOff DATE

SET @CutOff = '01/01/2016'

kherald69 SSC Eights! Points: 971 More actions · Answer 3

Ok, I just have to toss a wrench into the logic here.

Since we are talking about birthdays, what if the person is over 100 years old? This logic will fail.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

What type of data is being stored in the database? What is the application it is supporting?

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 5

SQLSeTTeR

SSCertifiable

Points: 5481

August 8, 2014 at 1:20 pm

#1736536

DOB

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

kherald69 (8/8/2014)
Ok, I just have to toss a wrench into the logic here.
Since we are talking about birthdays, what if the person is over 100 years old? This logic will fail.

It will fail in the same manner as any other attempt to measure beyond the scale implied. This has to be adjusted in the way that majority of the set is covered.

😎

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

Eirikur Eiriksson

SSC Guru

Points: 182911

August 8, 2014 at 2:34 pm

#1736549

Edit: Duplicate

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

SQLSeTTeR (8/8/2014)
DOB

It is was obvious that one of the data elements is DOB but that didn't answer the question.

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 9

My apologies.

It's a DOB field being stored as a string. No application is pointing to this data.

Any help is much appreciated.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

SQLSeTTeR (8/9/2014)
My apologies.
It's a DOB field being stored as a string. No application is pointing to this data.
Any help is much appreciated.

First, do not store dates as strings. Use the appropriate data type, and since you appear to be on SQL Server 2008 that would be DATE.

So you have an entire database with one table storing DOBs, why?