adding 0 to month - 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

SQL Server 2005 Forums

Transact-SQL (2005)

adding 0 to month

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-04-17 : 06:34:25

hi,
why does this query display the date as 20084, rather than 200804

SELECT
DATENAME(YEAR,getdate()) + RIGHT('00' + DATEPART(month,getdate()),2)

?

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-04-17 : 06:37:33

fixed it :

DATENAME(YEAR,getdate()) + RIGHT('00' + convert(varchar,DATEPART(month,getdate())), 2)

thanks anyway.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-17 : 06:38:53

change like this and try:-
SELECT
DATENAME(YEAR,getdate()) + RIGHT('00' + CAST(DATEPART(month,getdate()) as varchar(2)),2)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-17 : 06:39:31

because it's implicitly casting the '00' to an integer

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-17 : 06:40:29

quote:
Originally posted by jamie

fixed it :

DATENAME(YEAR,getdate()) + RIGHT('00' + convert(varchar,DATEPART(month,getdate())), 2)

thanks anyway.

Make sure you always specify a length for varchar field while casting

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 06:41:29

First rule is to keep it as simple possible.

SELECT CONVERT(CHAR(6), GETDATE(), 112)

E 12°55'05.25"
N 56°04'39.16"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-17 : 07:05:05

Another option (if you prefer an integer)...

SELECT DATEPART(year,getdate()) * 100 + DATEPART(month,getdate())

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-18 : 05:22:54

quote:
Originally posted by RyanRandall

Another option (if you prefer an integer)...

SELECT DATEPART(year,getdate()) * 100 + DATEPART(month,getdate())

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Another option

SELECT CONVERT(CHAR(6), GETDATE(), 112)*1

Madhivanan

Failing to plan is Planning to fail

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-04-22 : 08:50:23

thanks guys, useful

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-22 : 08:55:15

quote:
Originally posted by jamie

thanks guys, useful

But note that you should do formation at front end application if used

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources