convert date to month and year only.

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 to month and year only.

Author

Topic

pentahari
Starting Member

26 Posts

Posted - 2008-08-26 : 02:17:58

How to convert the date and time format to month/year format.

For ex:
1/1/2008
12/20/2008
1/30/2008

The Result:
1/2008
12/2008
1/2008

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-26 : 02:31:06

Try,
Select Cast( DatePart(month,YourDateColumn) as Varchar(5))+ '/' +Cast( Datepart(Year,YourDateColumn) as Varchar(5))

pentahari
Starting Member

26 Posts

Posted - 2008-08-26 : 02:38:43

quote:
Originally posted by sunil

Try,
Select Cast( DatePart(month,YourDateColumn) as Varchar(5))+ '/' +Cast( Datepart(Year,YourDateColumn) as Varchar(5))

Any chance to use like:

CONVERT(VARCHAR(12),view_date,110)

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-26 : 02:48:21

you can.

Select CONVERT(VARCHAR(12),datepart(month,view_date),110)+ '/' + CONVERT(VARCHAR(12),datepart(year,view_date),110)

But, I am not sure if you need to use dateformat like 110 or so. What I know is that these formats are used to convert date into different formats like mm dd yyyy, dd mm yyyy etc. In your case, I don't feel it is required.

pentahari
Starting Member

26 Posts

Posted - 2008-08-26 : 02:56:15

quote:
Originally posted by sunil

you can.

Select CONVERT(VARCHAR(12),datepart(month,view_date),110)+ '/' + CONVERT(VARCHAR(12),datepart(year,view_date),110)

But, I am not sure if you need to use dateformat like 110 or so. What I know is that these formats are used to convert date into different formats like mm dd yyyy, dd mm yyyy etc. In your case, I don't feel it is required.

My query is

select convert(varchar(12),dateadd(month, datediff(month, 0, voucher.vou_date), 0),110) as Number,sum(transact.camount) as amount,sum(transact.damount) as amount1 from transact inner join voucher on voucher.vouid=transact.vouid and voucher.vou_date>='1/1/2008' and voucher.vou_date<'9/1/2008' and transact.headid=113 and voucher.branchid=1 group by convert(varchar(12),dateadd(month, datediff(month, 0, voucher.vou_date), 0),110)

It is displaying the result:
03-01-2008 2000.0000 100.0000

but i want,
03/2008 2000.0000 100.0000

or
Mar,2008 2000.0000 100.0000

Thanks Advance,
PentaHari

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-26 : 03:02:52

Try

select Cast (DatePart(month,convert(varchar(12),dateadd(month, datediff(month, 0, voucher.vou_date), 0),110) ) as Varchar(5)) + '/' +
Cast (DatePart(Year,convert(varchar(12),dateadd(month, datediff(month, 0, voucher.vou_date), 0),110) ) as Varchar(5))
as Number,sum(transact.camount) as amount,sum(transact.damount) as amount1 from transact
inner join voucher on voucher.vouid=transact.vouid and voucher.vou_date>='1/1/2008' and
voucher.vou_date<'9/1/2008' and transact.headid=113 and voucher.branchid=1 group by
convert(varchar(12),dateadd(month, datediff(month, 0, voucher.vou_date), 0),110)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-26 : 03:39:03

can you do this in your front end application where the dates are being display ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 03:40:49

This is a formatting issue and i think it will be best to deal it in your front end application

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 03:41:10

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-26 : 03:41:23

quote:
Originally posted by pentahari

How to convert the date and time format to month/year format.

For ex:
1/1/2008
12/20/2008
1/30/2008

The Result:
1/2008
12/2008
1/2008

Where do you want to show converted dates?

Madhivanan

Failing to plan is Planning to fail

pentahari
Starting Member

26 Posts

Posted - 2008-08-26 : 03:47:19

quote:
Originally posted by sunil

Try

select Cast (DatePart(month,convert(varchar(12),dateadd(month, datediff(month, 0, voucher.vou_date), 0),110) ) as Varchar(5)) + '/' +
Cast (DatePart(Year,convert(varchar(12),dateadd(month, datediff(month, 0, voucher.vou_date), 0),110) ) as Varchar(5))
as Number,sum(transact.camount) as amount,sum(transact.damount) as amount1 from transact
inner join voucher on voucher.vouid=transact.vouid and voucher.vou_date>='1/1/2008' and
voucher.vou_date<'9/1/2008' and transact.headid=113 and voucher.branchid=1 group by
convert(varchar(12),dateadd(month, datediff(month, 0, voucher.vou_date), 0),110)

Thanks for your reply
It is correct query for my post.

select cast(month(dateadd(month, datediff(month, 0, voucher.vou_date), 0)) as varchar(128)) + '/' + cast(year(dateadd(month, datediff(month, 0, voucher.vou_date), 0)) as varchar(128))as Number,sum(transact.camount) as amount,sum(transact.damount) as amount1 from transact inner join voucher on voucher.vouid=transact.vouid and voucher.vou_date>='1/1/2008' and voucher.vou_date<'9/1/2008' and transact.headid=113 and voucher.branchid=1 group by cast(month(dateadd(month, datediff(month, 0, voucher.vou_date), 0)) as varchar(128)) + '/' + cast(year(dateadd(month, datediff(month, 0, voucher.vou_date), 0)) as varchar(128))

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-26 : 04:08:35

quote:
Originally posted by pentahari

quote:
Originally posted by sunil

Try

select Cast (DatePart(month,convert(varchar(12),dateadd(month, datediff(month, 0, voucher.vou_date), 0),110) ) as Varchar(5)) + '/' +
Cast (DatePart(Year,convert(varchar(12),dateadd(month, datediff(month, 0, voucher.vou_date), 0),110) ) as Varchar(5))
as Number,sum(transact.camount) as amount,sum(transact.damount) as amount1 from transact
inner join voucher on voucher.vouid=transact.vouid and voucher.vou_date>='1/1/2008' and
voucher.vou_date<'9/1/2008' and transact.headid=113 and voucher.branchid=1 group by
convert(varchar(12),dateadd(month, datediff(month, 0, voucher.vou_date), 0),110)

Thanks for your reply
It is correct query for my post.

select cast(month(dateadd(month, datediff(month, 0, voucher.vou_date), 0)) as varchar(128)) + '/' + cast(year(dateadd(month, datediff(month, 0, voucher.vou_date), 0)) as varchar(128))as Number,sum(transact.camount) as amount,sum(transact.damount) as amount1 from transact inner join voucher on voucher.vouid=transact.vouid and voucher.vou_date>='1/1/2008' and voucher.vou_date<'9/1/2008' and transact.headid=113 and voucher.branchid=1 group by cast(month(dateadd(month, datediff(month, 0, voucher.vou_date), 0)) as varchar(128)) + '/' + cast(year(dateadd(month, datediff(month, 0, voucher.vou_date), 0)) as varchar(128))

As suggested by sql gurus in this post, this is a formatting issue. Always try to do formatting in front end rather asking sql to do it for you. You can easily format what you want in front end with the ouput 03-01-2008 you are originally getting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources