handling odd date format

Question

Post reply

handling odd date format

Abhijit More

SSCertifiable

Points: 6897
More actions
August 21, 2015 at 6:05 am

#303649

Hi,
I need to handle date format "41981". Please help!
Abhijit - http://abhijitmore.wordpress.com

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Abhijit More (8/21/2015)
Hi,
I need to handle date format "41981". Please help!

So what date does 41981 represent?

A. April 1981 (M/YYYY)

B. April 19, 1981 (M/DD/YY or M/D/YYYY)

C. December 8, 2014 (Excel integer for a date)

D. Something else

You have to give us something to go on here.

If this is a column in a table, I hope your goal is to convert it into an actual date data type. Storing dates properly lets you use date math and prevents invalid data (like 12/34/2015) from getting into the table.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

guessing.....

declare @d as datetime = '1900-01-01'

select dateadd (dd,41981,@d) as calcdate

calcdate

2014-12-10 00:00:00.000

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 3

Good guess.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Yes, a very good guess. That would be the simplest one to handle, so let's hope for that one. 😉

So, the list is now:

A. April 1981 (M/YYYY)

B. April 19, 1981 (M/DD/YY or M/D/YYYY)

C. December 8, 2014 (Excel integer for a date)

D. The number of days since 01/01/1900

E. Something else

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

There's no good way to handle this unless you know the month, date, and year that are being entered. If I enter 19122011

Is this

Nov 20, 1912 or Dec 19, 1912?

Surely your application will let you guess, but it's possible to get confused. Really you want your application to validate that someone enters a month/day/year where you expect, but that can still cause issues. For example.

08042015

I enter that in my vacation system. It is seen at April 8, 2015, but I meant for it to be Aug 4, 2015. That's because we use different cultural date formatting mechanisms.

Once you know the month/day/year, you can CAST or CONVERT this to a date.

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 6

So, Abhijit More. Did any of this work for you or not? This IS a two way street here. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Yes, I'd like to see if one of the guesses was right or if it was something else.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Srikanth vathsala Right there with Babe Points: 746 More actions · Answer 8

Hi Abhijit,

Please try this,

Select CAST(41981 as datetime)

Output

2014-12-10 00:00:00

Thanks,

Srikanth s

Y.B. SSChampion Points: 11649 More actions · Answer 9

Those are always a pain...no guarantee the INT is based on days since 1900-01-01. I once had to export data from a 'very' old legacy system which the date was an INT based on days since 1938-06-30. Go figure... :hehe:

SELECT quote FROM brain WHERE original = 1
0 rows returned

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 10

OOOOOOHHHHHH!!!!! I GET IT!!! 😉 Now I know why the OP hasn't responded! He doesn't really need help. It was a ploy for him to post a link to blog out anyone accusing him of SPAM.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)