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.
| Author |
Topic |
|
readysetstop
Posting Yak Master
123 Posts |
 Posted - 2007-01-23 : 14:13:19
|
Any ideas how I can get a day of year to return as a three character string? For example, I can get...select datepart(dy,getdate()) to give me 23 for Jan 23rd. How would I get it to return 023? select convert(char(3),datepart(dy, 'Apr 9 2007')) Returns '99'.select cast(datepart(dy, 'Apr 9 2007') as char(3)) Also returns '99'. I have looked at DATEFORMAT but I don't see a format that would give me a three digit date. By way of explanation, I am trying to fit a date format decided on by my employer. Two digit year, three digit day of year. (07023, for Jan 23 2007).Any hints on what else I should be looking at?Thanks for any and all help.-D. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-23 : 14:34:57
|
| [code]declare @dt datetimeselect @dt = 'Apr 9 2007'select datediff(day, dateadd(year, datediff(year, 0, @dt), 0), @dt + 1)[/code]Peter LarssonHelsingborg, Sweden |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-01-23 : 15:02:56
|
select RIGHT(REPLICATE('0', 2) + cast(datepart(dy, 'Apr 9 2007') as varchar(3)), 3)Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-23 : 15:35:40
|
| There IS a "day-of-year" switch for DATEPART?Nice!A little bit cleaner...SELECT RIGHT('00' + DATENAME(dy, 'Apr 9, 2007'), 3)Peter LarssonHelsingborg, Sweden |
|
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2007-01-24 : 06:54:43
|
| Those last two solutions, spirit1's and Peso's second solution, worked perfectly. Thanks for the help.-D. |
|
|
|
|
|
|
|
|
Day of Year as char(3).