Date help needed

[ddrafts]

I'm not that good with SQL but I'm doing my best. I have been looking for over a week on how to do this but I cannot figure it out. I want the Closed Date and Time to just be the month. How or can this be done?

SELECT Incident."Incident #", Incident."Open Date & Time", Incident."Close Date & Time", Incident.Escalation, Incident."Priority:", Incident."Group Name", Incident."Department ID"
FROM {oj SDE."GAO NOTIFICATION".Incident Incident LEFT OUTER JOIN SDE.EXTERNAL_SUPPORT."Groups Details" "Groups Details" ON Incident."Open By Group" = "Groups Details".Sequence}
WHERE (Incident."Open Date & Time" Between {ts '2007-03-01 00:00:01'} And {ts '2007-03-31 00:00:01'}) AND (Incident."Group Name"='CLIENT SERVICES')
ORDER BY Incident."Close Date & Time", Incident.Escalation

Thanks in advance
Doug

 

[AlexCuse]

Doug -

Check out these queries. I think they will show you what to do, depending on your specific need.

[COLOR=green]--get a date/time value
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]getdate[/color]()

[COLOR=green]--get numeric representation of month
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]datepart[/color]([COLOR=#FF00FF]month[/color], [COLOR=#FF00FF]getdate[/color]())

[COLOR=green]--get name of month
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]datename[/color]([COLOR=#FF00FF]month[/color], [COLOR=#FF00FF]getdate[/color]())

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ddrafts]

Alex,

Thank you so much. what I ended up with was

SELECT Incident."Incident #", Incident."Open Date & Time", datename(month,convert(datetime,Incident."Close Date & Time")), Incident.Escalation, Incident."Priority:", Incident."Group Name", Incident."Department ID"
FROM {oj SDE."GAO NOTIFICATION".Incident Incident LEFT OUTER JOIN SDE.EXTERNAL_SUPPORT."Groups Details" "Groups Details" ON Incident."Open By Group" = "Groups Details".Sequence}
WHERE (Incident."Open Date & Time" Between {ts '2007-03-01 00:00:01'} And {ts '2007-03-31 00:00:01'}) AND (Incident."Group Name"='CLIENT SERVICES')
ORDER BY Incident."Close Date & Time", Incident.Escalation

It now works like I need it to.

 

[AlexCuse]

Are you storing dates as VARCHAR data type? If you aren't and its' a date-time column, you shouldn't need the convert. Of course, if they are varchar, you do need it. Just a thought.

Glad you got it working

Alex

Ignorance of certain subjects is a great part of wisdom