Date Convertion

[yesu prakash]

Hi, When i execute the query select convert(varchar(2),datepart(day,'2011-11-06')) the result i get is 6, i need to get the answer as 06 if only one digit, if 2 then no need of 0 in the first place. How to get that? Regards, YPKI

[Thats Aragon]

You can do something like below.

DECLARE @Input as varchar(10) = '2011-11-06'
select case when datepart(day,@Input) <= 9 then
cast('0' + cast(datepart(day,@Input) as varchar(1)) as varchar(2))
else cast(datepart(day,@Input) as varchar(2)) end AS NewDate

Regards

[Blue_Boy]

If you have always same format of datetime yyyy-MM-dd then you can use substring e.g select substring('2011-11-06',9,2)

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com

[Corporal Agarn]

If they are using a date column, I think this would be better select substring(Cast(@mydate as varchar(10)),9,2)

[Tim Carmichael]

How about: (corrected... thanks Luc) select right('0' + convert(varchar(2),datepart(day,'2011-11-06')), 2)

modified on Tuesday, June 7, 2011 3:49 PM

[Luc Pattyn]

left? :doh:

Luc Pattyn [My Articles] Nil Volentibus Arduum

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Please use <PRE> tags for code snippets, they improve readability.
CP Vanity has been updated to V2.3

modified on Friday, June 10, 2011 8:41 PM

[Tim Carmichael]

Good catch... fixed. It DID produce the correct answer, but that was happenstance, not design.

[jschell]

I suspect that you are trying to solve a problem which you have not stated. You asked about the solution that you think will solve the problem but if you stated the actual problem a better solution might exist.

[yesu prakash]

gr8...exactly what i required.. thankyou

[Thats Aragon]

Good call :) Have my 5.

Regards

[Prasanta_Prince]

SELECT CONVERT(VARCHAR(10), GETDATE(), 105)</pre>
Place your date inplace of Gatedate()