none
GetDate() as column header in export to csv file

    Question

  • I have an application that does a sql query and saves the data to a CSV file.  There are headers that have to be a specific way in the CSV file.  Basically I have 3 columns (Hour Ending, Yesterday, Today) in the CSV file, I can easily handle changing the H-E to Hour Ending however I need to change one columns header from City Load to Yesterdays date, and the 3rd column will not have any data being extracted from the database yet it still has to be there.

    Here is an example: The Hour column with the Hour Ending below it the City Load header needs to be Yesterdays date and then there is Todays date.  I have to submit it in this format.

    Hour	 6/30/2010		7/1/2010
    
    1	32464		
    
    2	32305		
    
    3	31202		
    
    4	30986		
    
    5	31588		
    
    6	32375		
    
    7	34874	
    
    8	37360	
    
    

     Here is my query string:

    "select [H-E] as Hour,[City Load] from City where RecordTime not in (select top ((select count(*) from City) - 8)RecordTime from City)order by [H-E]
    Friday, July 02, 2010 3:18 PM

Answers

  • Try using

     convert(varchar(10),dateadd(day,-1,getdate()),101) for Yesterday

    select convert(varchar(10),getdate(),101) for Today


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, July 02, 2010 3:41 PM
    Moderator
  • CREATE TABLE T(IDX INT,CNAME VARCHAR(60))
    INSERT INTO T VALUES(1,'A')
    
    
    DECLARE @YESTERDAY VARCHAR(10)
    DECLARE @TOTAY VARCHAR(10)
    DECLARE @SQL VARCHAR(MAX)
    SET @YESTERDAY=CONVERT(VARCHAR(10),GETDATE()-1,103)
    SET @TOTAY=CONVERT(VARCHAR(10),GETDATE(),103)
    SET @SQL=' SELECT IDX [HOUR ENDING],CNAME '+CHAR(39)+@YESTERDAY+CHAR(39)+',CNAME '+CHAR(39)+@TOTAY+CHAR(39)+' FROM T'
    EXEC(@SQL)
    
    DROP TABLE T
    
    
    HOUR ENDING	06/07/2010	07/07/2010
    1	       A	       A
    Wednesday, July 07, 2010 3:36 AM

All replies

  • Try using

     convert(varchar(10),dateadd(day,-1,getdate()),101) for Yesterday

    select convert(varchar(10),getdate(),101) for Today


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, July 02, 2010 3:41 PM
    Moderator
  • Naom,

    Just to make sure you understood me correctly the Column [City Load]  needs to be "Yesterday" (convert(varchar(10),dateadd(day,-1,getdate()),101)), and there won't be a column in the database for the "Today" however it needs to be sent to the CSV file as a header.

    I'm presumming this isn't this isn't the correct way to implement this seeing as I'm getting a syntax error.

    select [H-E] as Hour,[Net City Load] as convert(varchar(10),dateadd(day,-1,getdate()),101)

    Any thoughts?

    Friday, July 02, 2010 3:52 PM
  • You need to actually include one extra row with Headers, e.g.

    select 'Hour' as [Hour],convert(varchar(10),dateadd(day,-1,getdate()),101) as [City Load], convert(varchar(10),getdate(),101) as [Today]

    union all

    select [Hour], convert(varchar(10),myNumber), '' from myTable


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, July 02, 2010 3:57 PM
    Moderator
  • I know it's funny but yah, I actually have to include and extra column in the CSV file even though it doesn't and won't have any data in it or they won't except it when my Application submits it.

    Does it make a difference that I have a bit more to that select statement?

    Here is the full select statement:

    select [H-E] as Hour,[City Load] from City where RecordTime not in (select top ((select count(*) from City) - 24)RecordTime from City)order by [H-E]

    One issue, It is putting the dates in the area where the City Load Data should be.  What I was wanting was for these to be just the column headers, if it is possible.

    Friday, July 02, 2010 4:06 PM
  • Why is it putting the Date in the column instead of the City Load data.  The dates where just supposed to be the column headers.

    Any thoughts?

    Any assistance would be greatly welcomed.

    Tuesday, July 06, 2010 2:54 PM
  • What is your current select statement and DDL for the tables? My suggestion was to add the Headers row as a separate first row in the resulting file. You can do this with UNION ALL statement (all fields should be casted to character types, though).
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, July 06, 2010 4:17 PM
    Moderator
  • I'm a little confused as to how to implement your statement with my select statement. 

    select 'Hour' as [Hour],convert(varchar(10),dateadd(day,-1,getdate()),101) as [City Load], convert(varchar(10),getdate(),101) as [Today]

    union all

    select [Hour], convert(varchar(10),myNumber), '' from myTable

    The H-E and City Load are "Float".

    My select statement is:

    select[H-E] as Hour,[City Load]from City where RecordTime not in(select top ((select count(*) from City) - 8)RecordTime from City)order by [H-E]

    Any assistance would be greatly welcomed.

    Tuesday, July 06, 2010 4:38 PM
  • Whenever I try to run the code it gives me 10005 rows.  I have tried everyway I can think of in combining the code and I get the same results each time (10005 rows) when I should be just getting 24 rows.

    select

     

    [H-E] as Hour,convert(varchar(10),dateadd(day,-1,getdate()),101) as [City Load], convert(varchar(10),getdate(),101) as [Today] from City where RecordTime not in(select top ((select count(*) from City) - 24)RecordTime from City)

    union

     

    all

    select

     

    [H-E], convert(varchar(10),[City Load]), '' from City

    order

     

    by [H-E]

    anyone got any ideas?

    Tuesday, July 06, 2010 8:35 PM
  • Which SQL Server version you're using and what this where condition means?

    Also, is [H-E] the name of the column in the table? If yes, it's really weird one - confused me a lot...


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, July 06, 2010 9:03 PM
    Moderator
  • I'm using SQL Server 2005 Standard.

    The where condition gets the last 24 records that were entered into the database by recordtime and [H-E] and are sorted by [H-E].

    [H-E] is short for Hour Ending.  And yes it is a column heading.

    What i have to do is export the [H-E] as Hour and [City Load] as Yesterdays Date(i.e. 7/5/2010) along with an additional column (todays Date) to a csv file that is to be sent to an external web site.  The [H-E] goes from 1 to 24 for each hour of the day.  The [City Load] is the amount produced for that hour, However the column heading needs to be (Yesterdays date) so for example my csv file needs to look like.

    Hour,7/5/2010, 7/6/2010

    1, 1000,

    2,1005,

    3,1000,

    all the way to the 24th hour.

    Any Ideas?

    Wednesday, July 07, 2010 3:10 AM
  • CREATE TABLE T(IDX INT,CNAME VARCHAR(60))
    INSERT INTO T VALUES(1,'A')
    
    
    DECLARE @YESTERDAY VARCHAR(10)
    DECLARE @TOTAY VARCHAR(10)
    DECLARE @SQL VARCHAR(MAX)
    SET @YESTERDAY=CONVERT(VARCHAR(10),GETDATE()-1,103)
    SET @TOTAY=CONVERT(VARCHAR(10),GETDATE(),103)
    SET @SQL=' SELECT IDX [HOUR ENDING],CNAME '+CHAR(39)+@YESTERDAY+CHAR(39)+',CNAME '+CHAR(39)+@TOTAY+CHAR(39)+' FROM T'
    EXEC(@SQL)
    
    DROP TABLE T
    
    
    HOUR ENDING	06/07/2010	07/07/2010
    1	       A	       A
    Wednesday, July 07, 2010 3:36 AM
  • How exactly you're producing the file? I think we had a similar discussion a while back and Kalman suggested to put headers as the first row in the output that's why I made this suggestion. Right now I'm trying to remember the exact context of that suggestion...

    If you do output to a grid and then save as CSV this way, then last suggestion with dynamic SQL is a good one.

    Otherwise you need to go with my original suggestion.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, July 07, 2010 3:55 AM
    Moderator
  • Ok, I've figured out how to use this with my table, however I'm still not sure as to how to incorporate the other part of my select statement with the code.  I have tried this multiple different ways however I keep on getting multiple syntax errors.  I only need to grab the last 24 records inserted into the database.

     

    DECLARE @YESTERDAY VARCHAR(10)
    DECLARE @TODAY VARCHAR(10)
    DECLARE @SQL VARCHAR(MAX)
    SET @YESTERDAY=CONVERT(VARCHAR(10),GETDATE()-1,101)
    SET @TODAY=CONVERT(VARCHAR(10),GETDATE(),101)
    SET @SQL=' SELECT H_E [HOUR],[City Load] '+CHAR(39)+@YESTERDAY+CHAR(39)+ ',Today '+CHAR(39)+@TODAY+CHAR(39)+'where RecordTime not in(select top ((select count(*) from City) - 24)RecordTime from City)order by [H-E]'
    EXEC(@SQL)
    Any Ideas?
    Wednesday, July 07, 2010 1:30 PM
  • Naom,

    Figured it out.  Thanks for all the help.

    Wednesday, July 07, 2010 1:31 PM
  • Do you have Today field in your table? If not, use space(1) instead of ', Today ' in the script above. Also add space before 'Where ...

    and a ) at the end of the statement.

    Instead of Exec(@SQL) use PRINT @SQL and copy the statement from messages pane and try running it.

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, July 07, 2010 3:25 PM
    Moderator