Probably simple (1 Viewer)

[Kila]

I have an Transact SQL query that returns a field with a date in it within a temp table. How can I get it to convert this date (in the temp table only, of course) to display ONLY month & year, no day... mmyyyy or yyyymm or some variation thereof.

The resulting data generated by the query will be transferred into an Excel spreadsheet & I want the date to be treated as such so that the months will show up in order.

This is probably VERY simple, but I am a bit new to SQL & cannot figure it out.

I have the following, but I want the year in addition to the month number.

drop table #ESI
go
select prname,pcpteam,hfactname,count(distinct ptien)Uniq,
datepart(month,visitdate)month
into #ESI
from #fh
group by hfactname,pcpteam,prname,visitdate
go
select * from #ESI

Thanks in advance for any suggestions!

 

[SQL_Hell]

drop table #ESI
go
select prname,pcpteam,hfactname,count(distinct ptien)Uniq,
datepart(month,visitdate) + (datepart(year,visitdate) as visitdate
into #ES
from #fh
group by hfactname,pcpteam,prname,visitdate
go
select * from #ESI

 

[Kila]

Thank you. This is close, but it is adding them instead of putting them into a date.

drop table #ESI
go
select prname,pcpteam,hfactname,count(distinct ptien)Uniq,
datepart(mm,visitdate) + (datepart(yyyy,visitdate)) as EDdate
into #ESI
from #fh
group by hfactname,pcpteam,prname,visitdate
go
select * from #ESI

 

[SQL_Hell]

What do you mean adding them instead of putting them into a date?

 

[Kila]

I'm getting these...
2018
2013
2018
2020
2011
2015
2019
2017
2018
2020
2010

 

[SQL_Hell]

drop table #ESI
go
select prname,pcpteam,hfactname,count(distinct ptien)Uniq,
convert(varchar(2),datepart(mm,visitdate)) + convert(varchar(4),(datepart(yyyy,visitdate))) as EDdate
into #ESI
from #fh
group by hfactname,pcpteam,prname,visitdate
go
select * from #ESI

 

[Kila]

Thank you!!! Got that to work this way...

drop table #ESI
go
select prname,pcpteam,hfactname,count(distinct ptien)Uniq,
convert(varchar(2),datepart(mm,visitdate)) + convert(varchar(4),datepart(yyyy,visitdate))as EDdate
into #ESI
from #fh
group by hfactname,pcpteam,prname,visitdate
go
select * from #ESI

...but now how do I keep it from lopping off the leading 0? It even did it when I tried to put the year 1st.

102008
42009
102008
122008
22009
62009
etc...

 

[Kila]

Year 1st gave me this...
200810
20094
200810
200812
20092
20096

 

[SQL_Hell]

Have a look at this thread:


http://www.access-programmers.co.uk/forums/showthread.php?t=171850

 

[Kila]

Got it! Thanks so much!!!

drop table #ESI
go
select prname,pcpteam,hfactname,count(distinct ptien)Uniq,
case when len(cast(datepart(mm,EDdate) as varchar(2))) =1 then 
'0' + cast(datepart(mm,EDdate) as varchar(2)) + cast(datepart(yyyy,EDdate) as varchar(4))
else cast(datepart(mm,EDdate) as varchar(2)) + cast(datepart(yyyy,EDdate) as varchar(4))
end as EDdate
into #ESI
from #fh
group by hfactname,pcpteam,prname,EDdate
go
select * from #ESI