Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Month and Day of Datepart Expand / Collapse
Author
Message
Posted Wednesday, January 4, 2006 8:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:36 AM
Points: 57, Visits: 142

Hi,

 

I need to get a two digit day and month out of the datepart and it's only bringing back one digit.

 

select datepart(mm,getdate())

 

brings back 1 instead of 01.

 

Any advice will be appreciated.

 

Thanks,




Post #248107
Posted Thursday, January 5, 2006 2:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 3, 2008 6:45 AM
Points: 247, Visits: 26
select right('0' + convert(varchar,datepart(mm,getdate())),2)



Post #248344
Posted Thursday, January 5, 2006 8:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 23, 2016 7:09 PM
Points: 1,204, Visits: 301

select substring(convert(char(10),getdate(),101),1,2)

Post #248467
Posted Thursday, January 5, 2006 8:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:36 AM
Points: 57, Visits: 142

This is what I did but I think it stinks that the datepart function won't return the two digits even though you specify mm or dd.  Isn't that the reason for what instead of using m or d.

 

I had to rant a little. Sorry




Post #248473
Posted Thursday, January 5, 2006 11:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 2, 2016 1:53 PM
Points: 27, Visits: 95
Well, it is perfectly logical that it doesn't return a leading 0.   The Datepart returns an Integer value.   Integers are usually not expressed with leading 0s!  Which is why the conversion to a string is nescessary.
Post #248558
Posted Saturday, May 28, 2016 9:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 29, 2016 1:30 AM
Points: 1, Visits: 1
Thank you!
Post #1790489
Posted Monday, May 30, 2016 9:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 22, 2016 8:01 AM
Points: 374, Visits: 1,193
A new function is available: FORMAT

declare @d datetime = '2016-05-01';
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result'

SELECT FORMAT( @d, 'dd', 'en-US' ) AS 'DateTime Result'

- BUT:
Steer clear away from it. People with gazillion bragging points on this forum have cautioned that this is a performance killer if you want your solution to scale.

So yes, it is unfortunate, but the way to do it is to deal with strings. T-SQL is not really meant to be a formatting tool - such code is often moved to the reporting tool.
Post #1790659
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse