SQL Select statement with Date as

  • Thread starter Thread starter Pepe
  • Start date Start date
P

Pepe

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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Refresh a PivotTable using VBA (SQL generated Pivot) 2
Refresh Pivot after Creating (VBA and SQL) 1
How to Refresh with VBA(SQL) created Pivot 2
Can't Refresh PivotTable 3
Dates in a SQL Statement 1
Change NumberFormat and Cell Fill Colour in SELECT CASE Construct 2
Combo box date format changes when item selected from list 4
SQL statement with dates/times 2

Back
Top