SQL Select statement with Date as

[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

 

[Guest]

Pepe,

Try the "To_Char" method.

TO_CHAR(UpdateDate, 'YYYY - MM')

HTH

 

[Robin Hammond]

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]

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

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

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