0

I have a batch file that calls sqlcmd to run a command and then export the data as a csv. When viewed in a cell the trasancted date for example shows 35:30.0 but if you click on it the formula bar shows 1/1/1900 2:45:00 PM. I need the full timestamp to show in the cell. Any ideas?

The batch file is the following

sqlcmd -S server -U username -P password -d database -i "D:\path\sqlScript.sql"  -s "," > D:\path\report.csv -I -W -k 1

The script is the following. Now I currently have them cast as varchars, but that's simply because i've tried to change it a bit. Varchar doesn't work either.

SET NOCOUNT ON;

select top(10)BO.Status,
cast(tradeDate AS varchar) AS Trade_Date,
CAST(closingTime AS varchar) AS Closing_Time,
CAST(openingTime AS varchar) AS openingTime

FROM GIANT COMPLICATED JOINS OF ALL SORTS OF TABLES

mhopkins321
  • 2,993
  • 15
  • 56
  • 83

3 Answers3

1

You can add any character that is not recognized by Excel as a field delimiter before or after the date string (I used a colon), and the string will be preserved when the file is opened or imported into Excel.

Alternatively, you can do the import as is, and just set a custom format in Excel for the date cell(s): "m/d/yyyy hh:mm:ss A/P" should do the job.

chuff
  • 5,846
  • 1
  • 21
  • 26
0

Isn't this more of an excel formatting issue? Any way for you to check exactly what the SQL is returning? And what have you selected in the import wizard dialog for the cell formatting for the date column? (if you are just opening the CSV directly in Excel, try using the "text data" import instead)

alteredNate
  • 89
  • 2
  • 8
  • The sql data looks like `1900-01-01 14:45:00.000` and I am hoping to figure out a way to do this without a wizard – mhopkins321 Dec 11 '12 at 23:37
  • This is definately just a cell formatting problem. The data is fine, if you can't use the wizard or manually set the cell format after, it seems like you need to find a way to script the importation of the csv and cell formatting for the date columns, but unfortunately I can't help you with that one! – alteredNate Dec 11 '12 at 23:41
  • is there a way i can concatenate some data before or after so excel doesn't try to change the formatting and keeps it as whatever is in the cell? – mhopkins321 Dec 11 '12 at 23:43
0

Maybe csv2odf would work. It has a -d option that allows you to specify the format of the dates in your csv file. It also uses a template (xlsx) and you can format the way dates are displayed in Excel.

Larry1833681
  • 186
  • 4