smallint to general date

Question

Post reply

smallint to general date

TSQLExplorer

Ten Centuries

Points: 1279
More actions
January 27, 2006 at 4:06 pm

#89254

I have a table with many date fields, all set to datatype smallint with numbers like '13097'. I'd like to present this data in my TSQL results as a human readable "real" date.... something like 02/02/2005 . Is this possible, and if so, should the "CAST" or "CONVERT" command do this? I've tried those, with no satisfactory result.
Thanks all!

Viewing 7 posts - 1 through 6 (of 6 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

>>many date fields, all set to datatype smallint with numbers like '13097'

That's not really enough info to work with.

What does 13097 represent ? Is it the number of days (or hours or some other increment) from a certain point in time ? If yes, read BOL on the DateAdd() T-SQL function.

If not, we'll need more info.

TSQLExplorer Ten Centuries Points: 1279 More actions · Answer 2

It is the date of last inventory...so I'm guessing it's just a plain old date.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

>>so I'm guessing

Generally not a good recipe for a succesful implementation

13097 is not a date. You need to find out the business rules that determine how to derive a date from it.

Eg:

Select dateadd(dd, -13097, getdate())

This yields a date in 1970. So if I were to guess as to what your data contains, I'd guess it is the number of days since 1st Jan 1970. In which case

Select DateAdd(dd, YourColumnName, '01 Jan 1970') As LastInventoryDate

From YourTable

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

Or does 13097 really mean 1/30/1997? If so, how do you distinguish 1/12/97 from 11/2/97? Not enough info to give you a good answer on this.

And then again, I might be wrong ...
David Webb

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

It could also be a 2 digit year Julian date format in the form of DDDYY where "DDD" is the day of the year. But, as many have already said, not enough info to really make sure...

--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)

Vladan SSC-Insane Points: 22072 More actions · Answer 6

Looks very similar as a copy of Access table when pasted into a wrongly formatted Excel sheet (number instead of date). Didn't try it, but I think the same would happen if you import the column into SQL Server table smallint column - and I even have a vague recollection that something similar happened to me once when I relied on the wizard to import a table with dates... the column was created as smallint or int in SQLS. Please let us know how this situation occurred - if you are in the stage of importing data into your database from Access (or some other platform), it is possible that redefining the columns as datetime instead of smallint and importing the data again would be the best fix.