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