SQL Server YEAR() and Excel spreadsheets

Discussion in 'Programming' started by drewbe121212, Jun 18, 2007.

0
  1. #1
    Hello everyone,
    I have a dynamic report that is being generated into an excel spreadsheet. It is really interesting, because of the way I need to roll my totals, I need to pull out the data from the database with the month, day, year already split apart from the creationdate column of a record. It is very strange though

    
SELECT MONTH(CreatedDate), DAY(CreatedDate), YEAR(CreatedDate) FROM table
    Code (markup):
    In my rolling totals I break on the month and Year, though it seems to be acting very strange in excel. In the normal HTML display the year properly displays "2007", or whatever year it is currently on.

    HOWEVER, using CFDOCUMENT to generate a excel spreadsheet shows me in the year cell 2007:00:00. Now this wouldn't be a big deal to me except when I click on the cell the actual data within it is 3/23/1900 3:00:00 PM.

    What in the world is going on? Their is no reason why I see it would be doing this. It is just straight from SQL Server YEAR function to the spreadsheet.

    If anyone has any thoughts, ideas, or suggestions, please feel free to let me know as this has me completely stumped.
     
    drewbe121212, Jun 18, 2007 IP
  2. drewbe121212

    drewbe121212 Well-Known Member

    Messages:
    733
    Likes Received:
    20
    Best Answers:
    0
    Trophy Points:
    125
    #2
    OK, the problem is not with the database query, but excel itself. Excel is trying to tell my data what type it should be. It is automaticallying formatting my division cells with a % format (which makes them all "0%"), as well, on the year it thinks it should be "date format", even though it is an integer.

    Is their some way to force excel to use only generic formatting cells ?
     
    drewbe121212, Jun 18, 2007 IP
  3. drewbe121212

    drewbe121212 Well-Known Member

    Messages:
    733
    Likes Received:
    20
    Best Answers:
    0
    Trophy Points:
    125
    #3
    Ok, well I rigged it up a bit. Since excel was deciding to auto format the cell, I stuck a   character before the year display. Excel now thinks its a string and not a date. Its a bit of a rigged up solution, but it works. If anyone knows anything that is a little more specific to this issue and making sure excel doesn't try and decide what the cell formatting should be, I would appreciate the information.
     
    drewbe121212, Jun 18, 2007 IP
  4. igarcia

    igarcia Peon

    Messages:
    6
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    in your CFDOCUMENT with you call the variable you can try using a dateformat

    #DateFormat(variable, "YYYY")#
     
    igarcia, Jun 19, 2007 IP
  5. igarcia

    igarcia Peon

    Messages:
    6
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    if excel is actually running the problem you could create a MACRO to change the formatting for you
     
    igarcia, Jun 19, 2007 IP
  6. datropics

    datropics Peon

    Messages:
    309
    Likes Received:
    3
    Best Answers:
    1
    Trophy Points:
    0
    #6
    igarcia has a point there. Also to tell Excel that you sending explicit text, send the single quote as in '2007

    HTH

    daTropics
     
    datropics, Jun 21, 2007 IP
  7. drewbe121212

    drewbe121212 Well-Known Member

    Messages:
    733
    Likes Received:
    20
    Best Answers:
    0
    Trophy Points:
    125
    #7
    I tried the single quote and it showed up in excel as "'2007" displaying the single quote rather then not. How do you create a macro? I have never heard of/done that before?
     
    drewbe121212, Jun 22, 2007 IP