Converting datetime formats?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting datetime formats??

Author

Topic

Luckeraspect
Starting Member

2 Posts

Posted - 2014-03-20 : 11:20:46

I have a datetime format that comes in 'yyyy-mm-dd hh:mm:ss.nnn'

But I only want 'yyyy-mm'

I am not sure how to correctly write the select statements.
below is what I have..

how would I do this, so the datetime result is 'mmmm-yy' help please! thank you!

SELECT
[RecordingDate],
[RecordedUserID],
[CombindedPercentieScore]
FROM [dbo].[scoringsummary]

WHERE convert(varchar(7), [RecordingDate],120) between convert(varchar(7), DateAdd(dd,-180,GetDate()), 120) AND convert(varchar(7), DateAdd(dd,-1,GetDate()), 120)

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-03-20 : 12:41:10

FORMAT(fieldname, 'yyyy-MM')
It works in sql 2012. I am not sure about prior versions.

=======================
Not an Expert, Just a learner.
!_(M)_!

Luckeraspect
Starting Member

2 Posts

Posted - 2014-03-20 : 13:17:09

Well,
The table I was querying, is a view, and the datetime format wasn’t datetime I guess.. This is what our DBA told me,

So this worked

SELECT DISTINCT
convert(varchar(7),[RecordingDate],120) as [Date],
[RecordedUserICUserId] as [User],
[CombinedPercentileScore] as [Combined_Score_Percentile]
FROM
[dbo].[scoringsummary_viw]
WHERE convert(varchar(7),[RecordingDate],120) BETWEEN convert(varchar(7), DateAdd(dd,-365,GetDate()), 120) AND convert(varchar(7), DateAdd(dd,-1,GetDate()), 120)
AND [RecordedUserICUserId] IS NOT NULL
Group by convert(varchar(7),[RecordingDate],120), [RecordedUserICUserId], [CombinedPercentileScore]
Order by [Date], [User]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources