Excel datetime/concatenate question (SOLVED!)

Deebaser

Hey guys,

Im trying to concatenate a shitton of dates to insert into a temp table. However the second I concatenate the values in excel with single quotes + the INSERT statement, the fucking date loses its formating.

How am I doing it wrong?

Inquisitor77

Dates in Excel are treated as a number string. Any date you insert into Excel is automatically converted into a value of "ddddd", which counts the number of days since "January 0, 1900". This is what allows you to add and subtract dates, etc. within Excel. So if you copy/paste a date or call it within a function, you're going to be grabbing the actual number string (23554) rather than the actual "date" (July 27, 1954). (Those examples are fake, BTW.)

It depends on what you're trying to do, but if you get a number string as a result of a Concatenate, the easiest way to convert them back is to just pull everything you need, then Copy/Paste Special (Values) all of the cells and convert the format of the Pasted cells back into Date.

Deebaser

=CONCATENATE(" INSERT INTO #temptable (inv_date) VALUES (","'",C2,"')")

Where C2 is 12/15/2008 and my desired output is
INSERT INTO #temptable (inv_date) VALUES ('12/15/2008')

Someone on my team basically needs to create a temp table with a shitton of data and one of the joins she needs for a later step is datetime. I can't for the life of me figure out how to create the 7000 INSERTs that doesnt use excel.

Daenris

Have you tried using the TEXT function on the date first? If I concatenate two cells with dates, I get a concatenated version of Excel's numerical representation of dates. However, if I first do something like:
a1 = date1
a2 = date2
b1 = text(a1,"MM/DD/YYYY")
b2 = text(a2,"MM/DD/YYYY")
c1 = concatenate(b1,b2)

I get a concatenated version of of the dates. So in your example try:
=CONCATENATE(" INSERT INTO #temptable (inv_date) VALUES (","'",TEXT(C2,"MM/DD/YYYY"),"')")

edit: Just tried this and it works at least on Excel 2003.

Deebaser

Daenris, that worked PERFECTLY. I've never used the TEXT function before.
Thank you so very absolutely much!