Post reply

smallint to general date

  • 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!

  • January 27, 2006 at 4:13 pm

    #617593

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

  • January 27, 2006 at 4:20 pm

    #617594

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

  • January 27, 2006 at 4:41 pm

    #617596

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

     

     

  • January 27, 2006 at 5:35 pm

    #617599

    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

  • January 27, 2006 at 7:21 pm

    #617604

    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)
    Intro to Tally Tables and Functions

  • January 30, 2006 at 3:49 am

    #617677

    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.

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

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