how to insert date in the query result?

Question

Post reply

how to insert date in the query result?

fred2002

SSCrazy

Points: 2943
More actions
July 5, 2006 at 8:06 am

#118537

Hello,
i have a request in T sql and i would like to insert in the result file (xls) the date like:
created xx/xx/2006
results

Can somebody help?
Thanks
Fred

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 1

Can it will help you,

select *,left(Convert(varchar,getdate()),8) Create_date

from <your table>

Regards,

Amit Gupta..

RyanRandall SSChampion Points: 13623 More actions · Answer 2

Amit - that returns "Jul 5 2" for me.

fred - one of these will get the date in the format you need.

select convert(varchar, getdate(), 101) as created -->07/05/2006

select convert(varchar, getdate(), 103) as created -->05/07/2006

However, I've a feeling you're asking for more than this. If that's true, could you explain a bit more about what you need - ideally with an example.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

fred2002 SSCrazy Points: 2943 More actions · Answer 3

hello,

indeed it s not really what i need. I extract data from a TrackIT DB to an xls file. And i need in the xls file the date when the file was created like this:

created	07/05/2006

Nom_machine	Categorie	Num_Bul	Num_Ste	Departement	Nom
ITWJJJ	pc fixe	0119601	1002	Ipsos France
E04388	ecran	01340	01002	Ipsos France - Frais Généraux	?????????????

RyanRandall SSChampion Points: 13623 More actions · Answer 4

So the xls file already exists, and you want to add a couple of rows (rows 1 and 2, say) to it and put the date in row 1? And you want to do this using TSQL?

(Just to be clear )

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

fred2002 SSCrazy Points: 2943 More actions · Answer 5

fred2002

SSCrazy

Points: 2943

July 5, 2006 at 9:01 am

#647525

Hello Ryan,

exactly this.

RyanRandall SSChampion Points: 13623 More actions · Answer 6

To do exactly this, I think you need to use OLE Automation. You can read about that in BOL, or here:

http://www.databasejournal.com/features/mssql/article.php/1442201

I think Excel will need to be installed on your SQL Server box. I don't have that set up available right now, so I can't write an example. I can't find an example online either.

This is not straightforward (sadly), but I don't think it will be straightforward however you do it

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

It is possible with OPENROWSET. Read http://sqljunkies.com/WebLog/madhivanan/archive/2005/07/27/16233.aspx

N 56°04'39.16"
E 12°55'05.25"

RyanRandall SSChampion Points: 13623 More actions · Answer 8

Peso - Really?

You can use OPENROWSET to add 2 rows to the top of an Excel sheet which already has data in it and add the date there? Really?

(I hope you're right! )

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

Yes, if you name the sheet or a range in the sheet, Excel automatically adds the new rows at the first free row below existing. If no free rows are found, an error occur. This is my experience with OPENROWSET and Excel. This might have to do with version of Excel too, I haven't tested with Excel 2007 yet, but Office XP and 2003 supports this.

N 56°04'39.16"
E 12°55'05.25"

fred2002 SSCrazy Points: 2943 More actions · Answer 10

i never used OPENROWSET. Can i add it to my proc?

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 11

Yes Ryan. Of course I am right. Have I ever lied to you?

Use this code. Also tell Madhivanan to add this code to his otherwise excellent articles about Excel and OpenRowSet uses.

This is the principle of how to update a single cell in Excel using OpenRowSet. The same principle can be used to update a range of cells too.

update OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=c:\test.xls;hdr=no',

'SELECT * FROM [Sheet1$b7:b7]') set f1 = -99

You can also add formulas to Excel using this

update OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=c:\test.xls;hdr=no',

'SELECT * FROM [Sheet1$b7:b7]') set f1 = '=a7+c7'

But Excel has a latency problem with this. You have to enter the cell in Excel and press enter. I think this has to do with OpenRowSet exports the formula as text.

N 56°04'39.16"
E 12°55'05.25"