SQL Select statement with Date as
I'm pulling data into Excel using a SQL statement in VBA. I'd like to
pull an [updatedate] field and have it formatted in the format yyyy - mm. I'm not sure how to do this however. I basically have this set up right now. Statement = "SELECT Insertby, Campaign, Resolution1, " & _ "Resolution2, Resolution3, Stage, MonthlyCost, " & _ "ProspectId " & _ I'd like to add into this something along the lines of Format(UpdateDate,"yyyy - mm") AS UpdateMnthShrt I'm not sure how to set the UpdateDate portion of the statement. Any help would be GREATLY appreciated. Thanks Paul |
SQL Select statement with Date as
Pepe,
Try the "To_Char" method. TO_CHAR(UpdateDate, 'YYYY - MM') HTH |
SQL Select statement with Date as
Pepe,
Here's an idea which runs in Enterprise manager in SQL. Not sure how it will work on a query from Excel, but worth a shot: "SELECT Insertby, Campaign, Resolution1, Resolution2, Resolution3, Stage, MonthlyCost, ProspectId, CONVERT(char(4), YEAR(UpdateDate)) + '-' + CONVERT(char(2), MONTH(UpdateDate)) AS UpdateMnthShrt" Robin Hammond www.enhanceddatasystems.com "Pepe" wrote in message m... I'm pulling data into Excel using a SQL statement in VBA. I'd like to pull an [updatedate] field and have it formatted in the format yyyy - mm. I'm not sure how to do this however. I basically have this set up right now. Statement = "SELECT Insertby, Campaign, Resolution1, " & _ "Resolution2, Resolution3, Stage, MonthlyCost, " & _ "ProspectId " & _ I'd like to add into this something along the lines of Format(UpdateDate,"yyyy - mm") AS UpdateMnthShrt I'm not sure how to set the UpdateDate portion of the statement. Any help would be GREATLY appreciated. Thanks Paul |
SQL Select statement with Date as
Robin, Thank you for the advice. It worked nicely for me. I have an
additional challenge now for you or anyone willing to help out. I want to do something similar to what I did below, but this time convert the UpdateDate into a Week Range (Sun - Sat). The format would be 1/18/2004 - 1/24/2004 I had done something similar in Access with this formula: Int((CDate(UpdateDate))-Weekday((CDate(UpdateDate)))+1) & " - " & (Int((CDate(UpdateDate))-Weekday((CDate(UpdateDate)))+7)) AS UpdateWeekRange Thanks in advance for any help on this one. Paul "Robin Hammond" wrote in message ... Pepe, Here's an idea which runs in Enterprise manager in SQL. Not sure how it will work on a query from Excel, but worth a shot: "SELECT Insertby, Campaign, Resolution1, Resolution2, Resolution3, Stage, MonthlyCost, ProspectId, CONVERT(char(4), YEAR(UpdateDate)) + '-' + CONVERT(char(2), MONTH(UpdateDate)) AS UpdateMnthShrt" Robin Hammond www.enhanceddatasystems.com "Pepe" wrote in message m... I'm pulling data into Excel using a SQL statement in VBA. I'd like to pull an [updatedate] field and have it formatted in the format yyyy - mm. I'm not sure how to do this however. I basically have this set up right now. Statement = "SELECT Insertby, Campaign, Resolution1, " & _ "Resolution2, Resolution3, Stage, MonthlyCost, " & _ "ProspectId " & _ I'd like to add into this something along the lines of Format(UpdateDate,"yyyy - mm") AS UpdateMnthShrt I'm not sure how to set the UpdateDate portion of the statement. Any help would be GREATLY appreciated. Thanks Paul |
All times are GMT +1. The time now is 06:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com