Need help with Export to Excel

[leo57]

This is a 3 program issue: first I have an ASP.NET app. which saves data to SQL and has an export screen to save to Excel.
Is there anything I can do with this script to make the column be a date when exported using the .NET below. It shows as text looking like a date 5/12/13 or a 5 digit number such as 44987 (not the real date) in either example following.
The date column is currently this > DateAwarded(date, null)

SELECT CommonName, CONVERT(int, EmployeeEpriseID, 101) AS EmployeeEpriseID, Location,VPName, Nominator, ReportToManager, Report1, Report2, Report3, AwardType, [highlight #FCE94F]CONVERT(date, DateAwarded, 101) AS DateAwarded[/highlight], Event, NominationSummary FROM Recognition Where year(DateAwarded) = 2013 AND  VPName = 'fred flintstone' And ePrizeID = '12345678' ORDER BY UniqueID DESC

-- If I change the SQL above to this, it makes a real date but its text in Excel.
[highlight #FCE94F]CONVERT (nvarchar(12), DateAwarded, 101) AS DateAwarded[/highlight]

if I use this SQL it changes it to a date like 5/12/13 but then in Excel it text and the user wants a date.
The user does not want to have to reformat every export they do while in Excel.

Also here is the ASP.NET 4.0 code

ws.Cells("A1").LoadFromDataTable(tbl, True)
        'Write it back to the client
        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;  filename=" + ExcelFilename + ".xlsx")
        HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray())
        HttpContext.Current.Response.End()
        HttpContext.Current.ApplicationInstance.CompleteRequest()

 

[gmmastros]

just a guess, but try:

CONVERT (nvarchar(12), DateAwarded, [!]120[/!]) AS DateAwarded



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[leo57]

all that did was change it to 2013-10-28
but it is stll text.
I suspect since the "convert" is making it nvarchar, no matter what the format number is, it will always be text.
I also made the a testdate column as an nvarchar in SQL table and copied all dates to it.
The dates appear as a date in testdate but are text and not 5 digit number.
then
converted ran this SQL string it as such as

"SELECT CommonName, CONVERT(bigint, EmployeeEpriseID, 101) AS EmployeeEpriseID, Location," & _
                                "VPName, Nominator, ReportToManager, Report1, Report2, Report3, " & _
                                "AwardType, [highlight #FCE94F]CONVERT(Date, testdate, 101) AS DateAwarded,[/highlight] Event, Awarded, " & _
                                "NominationSummary FROM Recognition_test"

but that just made it a 5 digit number again.

this is probaby the wrong forum, but I wonder if something can be changed in this code that doeshe actual convert?

ws.Cells("A1").LoadFromDataTable(tbl, True)
        'Write it back to the client
        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;  filename=" + ExcelFilename + ".xlsx")
        HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray())
        HttpContext.Current.Response.End()
        HttpContext.Current.ApplicationInstance.CompleteRequest()

 

[SkipVought]

Convertt to a number and export.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[djj55]

You might want to not convert in SQL and convert through .NET. -- I am probably missing something here.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[TheBugSlayer]

Can you format the Excel column to Date before exporting it? What happens when you don't convert the date to text before exporting?

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[SkipVought]

I believe that you would have much better results PULLING data into Excel via MSQuery.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[leo57]

Skip, msquery; so how would this work, create a connection to SQL in Excel?

 

[SkipVought]

Data > Get External Data > From Other Sources > From SQL Server...

You will need the proper server name and other data required in the dialog.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]