PDA

View Full Version : Date format,crosstab in SQL statement



purplesky
04-15-2013, 08:14 PM
Hi, everyone

strSQL = "SELECT A,B,C " & _
"WHERE(A)LIKE'" & a & "%'" & _
"AND (C.TIME_STAMP)BETWEEN to_date('" & b & "','dd/mm/yyyy hh:MI:ss') AND to_date('" & b1 & "','dd/mm/yyyy hh:MI:ss')+1 " & _
"GROUP BY A,B,C "

I have such SQL statements in my excel vba. I succeed to retrieve the data and show the date. Bt it suppose to show date with time like 16/4/2013 7:05:34, bt it shows only 16/4/2013. When double click on the cell, then it will show the time and the above tab also will show include time when you double click.

How to I show it straight away on the cell without I double click it?
2. I have searched on google that "Transform and Pivot" are only use in Ms Access, it wouldn't work with Ms Excel. So instead of using that, which way can I use to retrieve data according to few columns?

Thanks everyone.

Jacob Hilderbrand
04-15-2013, 09:37 PM
Set the NumberFormat for the date columns.

Select the column, right click, format cells, number.

Then enter: d/m/yyyy h:mm:ss

purplesky
04-15-2013, 10:23 PM
Thank DRJ.
But why it wil show together with the I? I chose dd/mm/yyyy hh:Mi:ss

mohanvijay
04-16-2013, 06:12 AM
try this


Range("your range").NumberFormat = "dd/mm/yyyy hh:mm:ss"

sassora
04-16-2013, 01:03 PM
http://stackoverflow.com/questions/13953134/convert-access-transform-pivot-query-to-sql-server

purplesky
04-16-2013, 05:19 PM
Thanks mohanvijay. I got it.

Sassora, I did look on that link before I post, and I tried it I still cant get what I want so I decide to ask here. thanks. once I solved it I will mark as solved. Thanks for remind.