Date Conversion - 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

Date Conversion

Author

Topic

TPie9
Yak Posting Veteran

67 Posts

Posted - 2010-09-23 : 12:53:41

I have a table that has columns named Period and Year. The value in columnd Period is '1' and value in colun Year is '2010'.

My statement is as follows:
CONVERT(varchar(2), Period, 101) + '/01/' + CONVERT(varchar(4), Year, 101) and I get '1/01/2010'.

I need it to return '01/01/2010'. How can I do that? Also later in the table there are values 10, 11 and 12 in the period column.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 12:55:48

so what will be date for period 10? is it 01/10/2010 etc?

then you can use like


SELECT DATEADD(mm,Period-1,DATEADD(yy,Yearfield-1900,0))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TPie9
Yak Posting Veteran

67 Posts

Posted - 2010-09-23 : 13:02:30

The period column represents the month

TPie9
Yak Posting Veteran

67 Posts

Posted - 2010-09-23 : 13:03:32

The date for period 10 will be 10/01/2010

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:05:36

format you can do it at your front end application or use CAST/CONVERT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TPie9
Yak Posting Veteran

67 Posts

Posted - 2010-09-23 : 13:09:36

My statement is as follows:
CONVERT(varchar(2), Period, 101) + '/01/' + CONVERT(varchar(4), Year, 101) and I get '1/01/2010'.

How would I CAST/CONVERT it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:12:40

[code]SELECT CONVERT(varchar(11),DATEADD(mm,Period-1,DATEADD(yy,Yearfield-1900,0)),101)[/code]

I still recommend to do it at frond end app if at all possible

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TPie9
Yak Posting Veteran

67 Posts

Posted - 2010-09-23 : 13:14:19

I don't know what front end app you're referring to?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:18:14

quote:
Originally posted by TPie9

I don't know what front end app you're referring to?

that means application you're using at front end. it can a vb,.net,java etc app or it can be a reporting tool like ssrs,crystal report etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-24 : 05:52:31

you can also use

select dateadd(month,@month-1,dateadd(year,@year,0))

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-25 : 00:10:17

quote:
Originally posted by madhivanan

you can also use

select dateadd(month,@month-1,dateadd(year,@year,0))

Madhivanan

Failing to plan is Planning to fail

sorry how would that work ?
see below


declare @month int,@year int
select @month=10,@year=2010
select dateadd(month,@month-1,dateadd(year,@year,0)) AS Madhi,
dateadd(month,@month-1,dateadd(year,@year-1900,0)) AS Visakh

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-28 : 09:29:07

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

you can also use

select dateadd(month,@month-1,dateadd(year,@year,0))

Madhivanan

Failing to plan is Planning to fail

sorry how would that work ?
see below

declare @month int,@year int
select @month=10,@year=2010
select dateadd(month,@month-1,dateadd(year,@year,0)) AS Madhi,
dateadd(month,@month-1,dateadd(year,@year-1900,0)) AS Visakh

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I forgot to subtract 1900. Thanks

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 12:09:50

No problem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources