Convert Date? - 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

Convert Date?

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-03-30 : 14:50:13

Hello -

I have a date field and from that I only want they year and the month but dont know how to do that. For example I want December 2014......

Can anyone help?

Thanks,

John

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-30 : 15:13:04

datename(month,your_date_field)+' '+ltrim(year(your_date_field))

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-03-30 : 15:24:58

SELECT FORMAT( getdate(), 'yyyymm', 'en-US' )

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-03-31 : 07:14:39

Thank you, bitsmed. That worked perfectly.

Now I got a new problem though, my dates are sorted alphabetically now and I need them sorted numerically - january first, etc.....

Can you help with that?

Thanks again

quote:
Originally posted by bitsmed

datename(month,your_date_field)+' '+ltrim(year(your_date_field))

Maithil
Starting Member

29 Posts

Posted - 2015-03-31 : 07:30:01

select datename(month,your_date_field)+' '+ltrim(year(your_date_field)) from Table_name
Order by FORMAT(your_date_field,'MM')

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-03-31 : 08:08:58

How do I use the order by clause, Maithil?

I can't get it to work? Do I replace FORMAT in your example with the specific format like DATE??

quote:
Originally posted by Maithil

select datename(month,your_date_field)+' '+ltrim(year(your_date_field)) from Table_name
Order by FORMAT(your_date_field,'MM')

Maithil
Starting Member

29 Posts

Posted - 2015-03-31 : 08:16:29

Do you want Date also? for this??

Order by Clause is just to Show your date Monthwise...

Give me your example...

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-03-31 : 08:37:54

Sure, sir. Thanks again for you help, I really appreciate it.

Here is the example:

select datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))as IncurredDt
, sum(cl.NHPLiability) as IP_OP

from dw..ClaimLine cl
inner join dw..ClaimHeader ch
on ch.ClaimID = cl.ClaimID

where cl.ServiceDateFrom between @IncurredDateStart and @IncurredDateEnd
and ch.PaidDate between @PaidDateStart and @PaidDateEnd
and ch.SponsorId in (SELECT IDnumber FROM #Temp)
and ch.ClaimStatus in ('PAID','PAY')
and ch.ResubmittedClaimId = ''

group by datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))

order by datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))

quote:
Originally posted by Maithil

Do you want Date also? for this??

Order by Clause is just to Show your date Monthwise...

Give me your example...

Maithil
Starting Member

29 Posts

Posted - 2015-03-31 : 08:48:53

By
'order by datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))' it will Order it Alphabetically so April Month will always come first..

But Order by FORMAT(created,'MM')+ltrim(year(created))
January Month will always come first...

So You should use FORMAT to accomplish your requirement.

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-03-31 : 08:57:32

Thank you, I will give that a shot and let you know what happens.....

quote:
Originally posted by Maithil

By
'order by datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))' it will Order it Alphabetically so April Month will always come first..

But Order by FORMAT(created,'MM')+ltrim(year(created))
January Month will always come first...

So You should use FORMAT to accomplish your requirement.

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-03-31 : 09:01:01

Im getting errors. This is what I have:

order by FORMAT(created,'MM')+ltrim(year(created))

the error says that format is not a recognized built-in function?

John

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-31 : 09:11:15

FORMAT is new with SQL Server 2012. (Did you look it up? https://msdn.microsoft.com/en-CA/library/hh213505.aspx)

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-03-31 : 09:27:01

Oh, I should have specified I am using 2008 R2!!

quote:
Originally posted by gbritton

FORMAT is new with SQL Server 2012. (Did you look it up? https://msdn.microsoft.com/en-CA/library/hh213505.aspx)

Maithil
Starting Member

29 Posts

Posted - 2015-03-31 : 09:41:21

select datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))as IncurredDt
, sum(cl.NHPLiability) as IP_OP

from dw..ClaimLine cl
inner join dw..ClaimHeader ch
on ch.ClaimID = cl.ClaimID

where cl.ServiceDateFrom between @IncurredDateStart and @IncurredDateEnd
and ch.PaidDate between @PaidDateStart and @PaidDateEnd
and ch.SponsorId in (SELECT IDnumber FROM #Temp)
and ch.ClaimStatus in ('PAID','PAY')
and ch.ResubmittedClaimId = ''

group by datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))

order by datepart(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))
----------------------

Use datepart function for your request.

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-03-31 : 10:01:25

Thank you, Maithill!

quote:
Originally posted by Maithil

select datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))as IncurredDt
, sum(cl.NHPLiability) as IP_OP

from dw..ClaimLine cl
inner join dw..ClaimHeader ch
on ch.ClaimID = cl.ClaimID

where cl.ServiceDateFrom between @IncurredDateStart and @IncurredDateEnd
and ch.PaidDate between @PaidDateStart and @PaidDateEnd
and ch.SponsorId in (SELECT IDnumber FROM #Temp)
and ch.ClaimStatus in ('PAID','PAY')
and ch.ResubmittedClaimId = ''

group by datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))

order by datepart(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))
----------------------

Use datepart function for your request.

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-03-31 : 10:10:39

Im getting this error, Maithil:

Msg 8120, Level 16, State 1, Procedure StdPrc_ENDER_JB, Line 37
Column 'dw..ClaimLine.ServiceDateFrom' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

GRRRRRR

quote:
Originally posted by Maithil

select datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))as IncurredDt
, sum(cl.NHPLiability) as IP_OP

from dw..ClaimLine cl
inner join dw..ClaimHeader ch
on ch.ClaimID = cl.ClaimID

where cl.ServiceDateFrom between @IncurredDateStart and @IncurredDateEnd
and ch.PaidDate between @PaidDateStart and @PaidDateEnd
and ch.SponsorId in (SELECT IDnumber FROM #Temp)
and ch.ClaimStatus in ('PAID','PAY')
and ch.ResubmittedClaimId = ''

group by datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))

order by datepart(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))
----------------------

Use datepart function for your request.

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-03-31 : 10:36:28

I got it to work, theres more than one way to skin a cat!!!

I added a case statement to create numbers based on the month to sort by......this will work, I will always only have twelve periods......Here is what I did:

select datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))as IncurredDt
, case
when month(cl.servicedatefrom) = 01 then 01
when month(cl.servicedatefrom) = 02 then 02
when month(cl.servicedatefrom) = 03 then 03
when month(cl.servicedatefrom) = 04 then 04
when month(cl.servicedatefrom) = 05 then 05
when month(cl.servicedatefrom) = 06 then 06
when month(cl.servicedatefrom) = 07 then 07
when month(cl.servicedatefrom) = 08 then 08
when month(cl.servicedatefrom) = 09 then 09
when month(cl.servicedatefrom) = 10 then 10
when month(cl.servicedatefrom) = 11 then 11
when month(cl.servicedatefrom) = 12 then 12
end
as Sort
, sum(cl.NHPLiability) as IP_OP

from dw..ClaimLine cl
inner join dw..ClaimHeader ch
on ch.ClaimID = cl.ClaimID

where cl.ServiceDateFrom between @IncurredDateStart and @IncurredDateEnd
and ch.PaidDate between @PaidDateStart and @PaidDateEnd
and ch.SponsorId in (SELECT IDnumber FROM #Temp)
and ch.ClaimStatus in ('PAID','PAY')
and ch.ResubmittedClaimId = ''

group by datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))
, case
when month(cl.servicedatefrom) = 01 then 01
when month(cl.servicedatefrom) = 02 then 02
when month(cl.servicedatefrom) = 03 then 03
when month(cl.servicedatefrom) = 04 then 04
when month(cl.servicedatefrom) = 05 then 05
when month(cl.servicedatefrom) = 06 then 06
when month(cl.servicedatefrom) = 07 then 07
when month(cl.servicedatefrom) = 08 then 08
when month(cl.servicedatefrom) = 09 then 09
when month(cl.servicedatefrom) = 10 then 10
when month(cl.servicedatefrom) = 11 then 11
when month(cl.servicedatefrom) = 12 then 12
end
order by Sort

The code is longer but it works!!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-31 : 10:58:31

So...when would this:


case
...
when month(cl.servicedatefrom) = nn then nn
...
end as Sort

ever be different from


month(cl.servicedatefrom) as Sort

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 05:11:09

quote:
Originally posted by jcb267

group by datename(month,cl.servicedatefrom)+' '+ltrim(year(cl.servicedatefrom))

I would expect that to perform far worse than


GROUP BY DATEADD(Month, DATEDIFF(Month, 0, cl.servicedatefrom), 0)

that calculation is purely artithmetic, so "light" on CPU. It will "round" all dates to the 1st of the month and thus suitable for grouping / sorting

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 05:14:03

P.S. You can, similarly, "round" date to Day (effectively removing any TIME component), or even YEAR etc.


DATEADD(Day, DATEDIFF(Day, 0, MyDateColumn), 0)
...
DATEADD(Year, DATEDIFF(Year, 0, MyDateColumn), 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources