Click here to Skip to main content
15,879,613 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I'm trying to prepare an insert query from the table of data in excel. When i try the below formula in excel, the date value is not getting properly. The date is showing as number. Please assist me to get the date value in the Query field.

What I have tried:

Formula i used,
="INSERT INTO #TEMP VALUES("&A2&","&B2&",'"&C2&"','"&D2&"')"

Result:
ID	MAPPING_ID	WEF	        VALID_UNTIL	QUERY
1	700696	    2022-09-01	2022-12-01	INSERT INTO #TEMP VALUES(1,700696,'44805','44896')


Required Output:
ID	MAPPING_ID	WEF	        VALID_UNTIL	QUERY
1	700696	    2022-09-01	2022-12-01	INSERT INTO #TEMP VALUES(1,700696,'2022-09-01','2022-12-01')
Posted
Updated 22-May-23 23:06pm
Comments
Richard MacCutchan 23-May-23 3:40am    
Dates and Times in Excel are stored as floating point values. They only appear in human readable form when a format is attached to them.

1 solution

You need to format the date into a string - as this is a formula then the Excel function you need is TEXT - TEXT function - Microsoft Support[^]

e.g.
="INSERT INTO #TEMP VALUES("&A2 &","&B2 &",'"&TEXT(C2,"YYYY-MM-DD")&"','"&TEXT(D2, "YYYY-MM-DD")&"')"
 
Share this answer
 
v2
Comments
Rajesh waran 23-May-23 6:55am    
Thank you! It works.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900