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 |
|
mufasa
Yak Posting Veteran
62 Posts |
 Posted - 2004-01-23 : 14:10:38
|
Something that seems simple but is not.I have a table that has a year and a week fieldBoth are intFor selection purposes in Crystal reports, I need to combine these fields as one for users.This field is part of a large query that returns results based on style, week.I need the selection field to look like 2003-01I tried using Convert to char and I get it to look like 2003-1 but not 2003-01This causes a problem in when I need to return the min and max date.Example, in my Report I need to display the beginning and ending periods. If I were to choose week 3 to 15 year 2003, the report would return 2003-10 to 2003-3Is there any simple solution?Mufasa  |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-01-23 : 14:28:10
|
| The simple solution is to use a datetime, not some other construct that separates out its components.Use a computed column to create the "yyyy-wk" string. If you prepend a zero to week #'s < 10 you will have a string that sorts lexically in date order. This is all you need for max/min.If you want to perform other datetime-like operations, such as computing the difference between two dates ... well, you start to see why a datetime is the best path to take all round.JonathanGaming will never be the same |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-23 : 14:49:12
|
| SELECT SUBSTRING(CONVERT(CHAR(8), GETDATE(), 112), 1, 6)Tara |
|
|
|
mufasa
Yak Posting Veteran
62 Posts |
Posted - 2004-01-23 : 15:11:05
|
| I can not use a computed date, since our corp. calendar does not always follow system weeks.Sometimes I have to change previous years weeks when there are 53, like last year.I have a table called Rep_Calendar which has columns week, month and year.Thanks Jonathan I was able to use the code below with your suggestionsSELECT CONVERT(CHAR(4), dbo.Rep_Calendar.[Year]) + ' - ' + CASE WHEN dbo.Rep_Calendar.Week < 10 THEN '0' + CONVERT(CHAR(2), dbo.Rep_Calendar.Week) ELSE CONVERT(CHAR(2), dbo.Rep_Calendar.Week) END AS PeriodFROM dbo.Rep_CalendarThanks AgainMufasa |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2004-01-28 : 13:05:49
|
| Another angle to do this could be, add the week to 100 and trim it from the right 2 places gives a leading 0 to weeks below 10SELECT CONVERT(char(4),[Year]) + '-' + CONVERT(char(2),RIGHT(100 + Week,2)) AS PeriodFROM dbo.Rep_Calendar |
|
|
|
|
|
|
|
|
'CONVERT HELP'