How Do I Zero-Fill an Int when converted to Char?
-
This seems like it ought to have a simple answer, but I haven't found one...
I want to surface a derived column in a View (based on a date column) that's formatted as YYYY-WW. I've read and re-read the CAST and CONVERT documentation and I can't figure out how to get the WW portion (week number) to be left-padded with zero when the week number < 10. (e.g. I want '2004-01' but I get '2004-1') I know that I could use a Case statement to concatentate a '0' when WeekNbr < 10, but there ought to be a simpler way...
-
CASE statement,
sorry
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Or you could do something confusing to read like:
CAST(DATEPART(yyyy, MyDateCol) AS VarChar(4))
+ '-'
+ CAST( RIGHT(CAST('0' as VarChar(1)) + CAST(DATEPART( ww, MyDateCol) AS VarChar(2)),2) AS VarChar(2))
Which concatenates a leading 0 onto all week values then takes the right 2 digits. Have to cast each part as char type to keep SQL from treating them as numerics.
I'm not sure this qualifies as 'simpler' though
-
This is the better method but I think you added a bit much
CAST(DATEPART(yyyy, myDateCol) AS VarChar(4))
+ '-'
+ RIGHT('0' + CAST(DATEPART( ww, myDateCol) AS VarChar(2)),2)
-
Thanks! The RIGHT operator was the trick I was missing. This is bound to be faster (and somewhat easier to read) than the CASE construct...
CAST(DATEPART([year], @dt) AS CHAR(4)) +
'-' +
CASE WHEN DATEPART([month], @dt)< 10
THEN ('0' + CAST(DATEPART([month], @dt) AS CHAR(1)))
ELSE CAST(DATEPART([month], @dt) AS CHAR(2))
END
-
This would be better
CAST(DATEPART(yyyy, @dt) AS CHAR(4)) + '-' + RIGHT('0' + CAST(DATEPART(m,@dt) AS VARCHAR(2)),2)
-
Heh heh...
I was working on a slightly different solution involving REPLICATE() when I thought of using RIGHT(), I left in that outside convert by accident. You're right Antares, it turns it into a much simpler piece of code that way. Also I guess by placing the leading 0 in quotes as '0' it tells SQL server that it is dealing with a CHAR type so didn't have to convert that one either.
It's definitely cleaner than the CASE way too.
-
Just wanted to point it out. Has no real major barring on the code as a whole except more compact. There is probably even better ways I have seen or thought of yet.
-
Here is another option you can use for the zero-padding for the week:
REPLICATE('0', 2-LEN(DATEPART( ww, myDateCol))) + CAST(DATEPART( ww, myDateCol) AS varchar(2))
-
Let's make it even smaller
CAST(DATEPART(yyyy, datecol ) AS VarChar(4))
+ '-' + RIGHT('0' + DATENAME( wk, datecol) ,2)
-
CONVERT(char(5),@dt,120)+RIGHT('0'+DATENAME(ww,@dt),2)
-
Does make it smaller code wise but in memory it has to do extra work setting up the format from the convert (note also does a table read in master to get the format) then chops it off. So code wise smaller, but I think work wise and memory wise will total more.
-
Ah-ha, let's take a bit further (note I never used DATENAME before so forgot it was there and that it returns a character string as opposed to an interger).
DATENAME( yyyy, datecol) + '-' + RIGHT('0' + DATENAME( wk, datecol) ,2)
-
I am not sure, how you thought of a table read in master.
Query Plan shows only constant scan for this type of query.
select CAST(DATEPART(yyyy, '2003-01-10') AS VarChar(4))
+ '-' + RIGHT('0' + DATENAME( wk, '2003-01-10'),2)
-
Sorry you missed the quote item. I was referring to using CONVERT in the other presented solution.
However do look at my other post jsut before yours.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply