Numeric Formatting - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Numeric Formatting

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-26 : 13:27:33

I recently received some "outstanding" help on a large query I've been working on, and was hopeing to get some additional help on a couple of problems that have me stumped.

1: In the query I have the following code.

WHEN losttime.CTwentyOne.C23DiscussionDate >= CTwentyOne.AbsenceStopDate
THEN 'Completed'ELSE CONVERT(varchar(20),DATEDIFF(dd,CTwentyOne.AbsenceStopDate, getdate()))
END AS DaysOverDue,

Is there a way of formatting the return values so that when I do the following.

ORDER BY losttime.CTwentyOne.DaysOverDue DESC,

The results are in descending order? Right now the numbers are in a descending order but out of sink. I'm thinking I just need to format the value as (000), but can't seem to figure out how to do it.

2. My other question is that I have the following code.

DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)AS AbsenceMonth,

Which returns the name of the month. Is there a way of having it return the name for just the current month Ie: September. And the last three months as well. Ie: August, July, June. Anything between January - May of 2003 I would like it to just return the year Ie: 2003, and if the absence happened in 2002 to just show Ie: 2002.

Thanks for all your help now and in the past!

Regards.

GC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-26 : 13:33:02

What do you mean by out of sync? Could you show us an example?

Tara

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-26 : 13:43:22

Tara,

This is what I'm getting.

April 90
June 90
June 90
June 90
June 90
September 9
September 9
September 9
September 9
September 9
July 80
July 80
July 80
September 8
September 8
September 8
September 8
September 8
August 26
August 26
January 259
January 259
January 257
January 254
January 252
January 251
January 251
January 250
September 25
September 25
September 25
September 25
September 20
September 20
September 20
September 2
September 2
September 2
September 2
September 2

I think my problem would be solved if I could format the column to three digits. Ie: 002 or 020. so that the descend order would be 285
020
002
etc...

Thanks again.

Gary

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-26 : 14:07:39

For No. 2

DATENAME(MONTH, getdate()), DATENAME(MONTH, (DATEADD(month, -1, GETDATE())), DATENAME(MONTH, (DATEADD(month, -2, GETDATE())), DATENAME(MONTH, (DATEADD(month, -3, GETDATE()))

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-26 : 14:14:02

To add leading zeroes:

right(('000' + your field), 3) --- with 3 being digits needed in outcome

results would resemble:
003
033
333

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-26 : 14:31:39

Drymchaser,

I'm still stumped. Where does the "Right" fall into this line of code?

WHEN losttime.CTwentyOne.C23DiscussionDate >= CTwentyOne.AbsenceStopDate
THEN 'Completed'ELSE CONVERT(varchar(20),DATEDIFF(dd,CTwentyOne.AbsenceStopDate, getdate()))
END AS DaysOverDue,

I've got all my SQL books open and the only examples show it at the start of the SELECT. Help!!

GC

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-26 : 14:46:43

(Question no 1) The results you are getting are correct, since you are sorting them as varchar(20), i.e. by the total ascii values of the characters in the string. You could cast DaysOverDue to an INT and get it sorted right but I think you also have a value of 'Completed' for some of the rows that would throw a type mismatch error. So you need to check the datatype and sort accordingly:

SELECT blah, blah, blah
FROM table1
ORDER BY
CASE WHEN IsNumeric(DaysOverDue) THEN DaysOverDue
ELSE 0 END DESC --Use 0 to see all rows with "completed" first

Owais

Make it idiot proof and someone will make a better idiot

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-26 : 15:11:26

Gary,

Tell me what "20" comes from in this result
September 20

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-26 : 15:39:58

Drymchaser,

My example wasn't the greatest as I was showing the results from two different fields.

The 'DaysOverDue' field is just a calculation between two dates. I get the results I need, I would just like to add some leading zeros so that I can get the query to display in a descending order.

Gary

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-26 : 17:12:43

Gary --

Use the DateDiff() function, it can return the # of days between two dates. And it returns a numeric value, so you can sort by it.

- Jeff

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-26 : 17:49:14

Jeff,

Here's the code I am using which includes DATEDIFF, I'm getting the results I need, I just need them formatted to 3 digits so that they sort in descending order properely.

WHEN losttime.CTwentyOne.C23DiscussionDate >= CTwentyOne.AbsenceStopDate
THEN 'Completed'ELSE CONVERT(varchar(20),DATEDIFF(dd,CTwentyOne.AbsenceStopDate, getdate()))
END AS DaysOverDue,

Right now the sort comes back as.

93
92
91
90
9
89 to 80 then
8
79 to 70 then
7....etc.

When we get down to the 3's then it's

365 to 300 then
39 to 30 then 3. This is the same for the 2's and 1's.

I just need it descend in order.

365

to

001

GC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-26 : 18:07:18

WHY are you converting the result to a VARCHAR ?! the result of DateDiff() is a perfectly good number that you can return + sort on. Does this make sense?

You need two columns: 1 called "days over due" and 1 called "Status" or something like that. You should not try to mix words and numbers in the same column. Or, just return a days over due #, and in your PRESENTATION LAYER (HTML, Crystal, Access, Excel) take care of formatting and display the words "completed" instead.

PLease think about this and try it out and tell me if it makes sense.

- Jeff

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-27 : 06:07:00

Gary, did you miss my post in the middle of this page?

Owais

Make it idiot proof and someone will make a better idiot

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-29 : 14:04:31

Jeff,

I read what you had to say on Friday before I left the office and thought about what you had said over the weekend.

From the query I have now with the help of the SQL Team Forum members, I am getting the results I need. I just wanted to "tweak" it a bit more. The conversion in the CASE Statement to VARCHAR does give us the result we need of either text "Completed" or a number.

I've played around with the query and found that I can get the result I need by descending the AbsenceStopDate in "Ascending Order".

In another part of the query I was using the "CONVERT" function to change the date fields to CHAR and the Style to 101 to give me a small date of MM/DD/YYYY in all of the date fields. I've looked through the SQL books I have and can't find anything that shows how to get the Date fields to just a small date "MM/DD/YY"

If I convert them then I can't get them to sort properly as well, because of the CHAR format which is the same problem as the DaysOverDue field.

Any suggestions on the dates to change them to just a small format?

Thanks again for your time on this.

GC

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-29 : 14:07:49

How about:

SELECT CONVERT(varchar(8),GetDate(),1)

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-29 : 14:27:19

Brett,

I tried but still get the same problem. Converting the date to a VARCHAR will not let me sort the field in a Descending Order by date.

If I leave it to just the default date format I can sort things fine.

In the date field I just want to get rid of the Time area. It's all 00:00:00 which I don't need.

Thanks.

GC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-29 : 15:22:13

Gary --

Formatting should be done at the PRESENTATION LAYER not at the database layer. Just return the RAW DATA and format the data as nicely as you like in your PRESENTATION.

how are you displaying the results of this query to users? Web page? Report? Excel?

There is a REASON why SQL doesn't have lots of formatting functions -- because it shouldn't.

If you leave the data unformatted while in SQL, you will find you have no trouble sorting and grouping and doing all the things you need to do.

- Jeff

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-29 : 15:36:03

Jeff,

The little light just turned on. I hear what you are saying. I was too busy formatting my query in the SQL Query Analyzer and not thinking of the "BIG" picture.

I am hoping to have the query result display on a web page and also in an Excel report.

Thanks for the friendly nudge in the right direction. If I run into any problems I'll get back with you.

Thanks again.

GC

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-29 : 15:42:41

Gary,

What does SELECTing a column and ORDERing it have to do with each other?

I agree with Jeff, but isn't this simple?


USE Northwind
GO

  SELECT CONVERT(varchar(8),OrderDate,1) 
    FROM Orders
ORDER BY OrderDate
GO

No?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-09-29 : 16:19:18

Brett,

I keep copies of my queries as I build in steps so that if something blows I have a backup that works. I've tried both queries, one where I just leave the date fields alone and SQL defaults as 10/05/2002 0:00
, and the other where I have CONVERTED the date field as CONVERT (Char (10), losttime.CTwentyOne.AbsenceStartDate, 101)as AbsenceStartDate.

I sort both by: ORDER BY losttime.CTwentyOne.AbsenceStopDate DESC

The one query using the convert returns this.

Absence Code Absence Start Date Absencen Stop Date Absence Month C23 Due Days Over Due Days C23 Completed
UA 12/31/2002 12/31/2002 December YES 272 Needed
SKU 12/18/2002 12/31/2002 December YES 272 Needed
SK 12/26/2002 12/31/2002 December YES 272 Needed
SK 12/23/2002 12/30/2002 December YES 273 Needed
SKU 12/29/2002 12/30/2002 December YES 273 Needed
SKQ 12/28/2002 12/28/2002 December YES 275 Needed
SK 12/22/2002 12/23/2002 December YES 280 Needed
RL 12/23/2002 12/23/2002 December YES 280 Needed
SK 12/20/2002 12/20/2002 December YES 283 Needed
RL 12/18/2002 12/18/2002 December YES 285 Needed
SK 12/18/2002 12/18/2002 December YES 285 Needed
SK 12/10/2002 12/17/2002 December YES 286 Needed
SK 12/14/2002 12/16/2002 December YES 287 Needed
SK 12/13/2002 12/16/2002 December YES 287 Needed
SK 12/13/2002 12/14/2002 December YES 289 Needed
SK 12/10/2002 12/12/2002 December YES 291 Needed
SK 12/07/2002 12/09/2002 December YES 294 Needed
RL 12/09/2002 12/09/2002 December YES 294 Needed
SK 12/02/2002 12/08/2002 December YES 295 Needed
SK 12/06/2002 12/06/2002 December YES 297 Needed
SK 12/03/2002 12/06/2002 December YES 297 Needed
RL 12/06/2002 12/06/2002 December YES 297 Needed
SK 12/05/2002 12/05/2002 December YES 298 Needed
RL 12/03/2002 12/03/2002 December YES 300 Needed
UA 12/02/2002 12/02/2002 December YES 301 Needed
SK 12/02/2002 12/02/2002 December YES 301 Needed
SK 11/30/2002 12/01/2002 November YES 302 Needed
SK 11/27/2002 11/27/2002 November YES 306 Needed
SKU 11/26/2002 11/27/2002 November YES 306 Needed
RL 11/26/2002 11/26/2002 November YES 307 Needed
UA 11/23/2002 11/23/2002 November YES 310 Needed
UA 11/20/2002 11/20/2002 November YES 313 Needed
SK 11/18/2002 11/19/2002 November YES 314 Needed
NC 11/16/2002 11/16/2002 November YES 317 Needed
SK 11/12/2002 11/12/2002 November YES 321 Needed
RL 11/06/2002 11/06/2002 November YES 327 Needed
SKU 11/01/2002 11/01/2002 November YES 332 Needed
SKL 10/01/2002 10/30/2002 October YES 334 Needed
SK 10/28/2002 10/29/2002 October YES 335 Needed
SK 10/21/2002 10/29/2002 October YES 335 Needed
SK 10/20/2002 10/21/2002 October YES 343 Needed
NC 10/19/2002 10/19/2002 October YES 345 Needed
RL 10/08/2002 10/08/2002 October YES 356 Needed
SK 10/05/2002 10/05/2002 October YES 359 Needed
SK 09/23/2003 09/25/2003 September YES 4 Needed
SK 09/25/2003 09/25/2003 September YES 4 Needed
SK 09/23/2003 09/25/2003 September YES 4 Needed
SK 09/25/2003 09/25/2003 September YES 4 Needed
SK 09/24/2003 09/25/2003 September YES 4 Needed
SKU 09/18/2003 09/25/2003 September YES 4 Needed
SKU 09/16/2003 09/25/2003 September YES 4 Needed
SK 09/25/2003 09/25/2003 September YES 4 Needed
SK 09/24/2003 09/25/2003 September YES 4 Needed
SK 09/25/2003 09/25/2003 September YES 4 Needed
SK 09/24/2003 09/25/2003 September YES 4 Needed
SK 09/23/2003 09/25/2003 September YES 4 Needed
SKU 09/25/2003 09/25/2003 September YES 4 Needed
SKL 09/08/2003 09/25/2003 September YES 4 Needed
SK 09/24/2003 09/25/2003 September YES 4 Needed
SK 09/25/2003 09/25/2003 September YES 4 Needed
SK 09/25/2003 09/25/2003 September YES 4 Needed
SK 09/23/2003 09/25/2003 September YES 4 Needed
SK 09/25/2003 09/25/2003 September YES 4 Needed
SK 09/25/2003 09/25/2003 September YES 4 Needed
SKL 09/02/2003 09/25/2003 September YES 4 Needed
SKL 09/07/2003 09/25/2003 September YES 4 Needed
SKU 09/19/2003 09/25/2003 September YES 4 Needed
SK 09/24/2003 09/25/2003 September YES 4 Needed
SK 09/25/2003 09/25/2003 September YES 4 Needed
SKL 06/30/2003 09/25/2003 June YES 4 Needed
SKL 07/30/2003 09/25/2003 July YES 4 Needed
SK 09/18/2003 09/25/2003 September YES 4 Needed
SK 09/25/2003 09/25/2003 September YES 4 Needed
UA 09/25/2003 09/25/2003 September YES 4 Needed
SKP 08/21/2003 09/25/2003 August YES 4 Needed
SKL 07/15/2003 09/25/2003 July YES 4 Needed
SK 09/17/2003 09/25/2003 September YES 4 Needed
SKL 08/14/2003 09/25/2003 August YES 4 Needed
SKL 07/23/2003 09/25/2003 July YES 4 Needed
SK 09/14/2003 09/25/2003 September YES 4 Needed
SKL 09/07/2003 09/25/2003 September YES 4 Needed
SKL 04/17/2003 09/25/2003 April YES 4 Needed
SKL 07/23/2003 09/25/2003 July YES 4 Needed
SKL 05/05/2003 09/25/2003 May YES 4 Needed
SK 09/17/2003 09/25/2003 September YES 4 Needed
SKL 07/31/2003 09/25/2003 July YES 4 Needed
SKL 09/11/2003 09/25/2003 September YES 4 Needed
SKU 09/25/2003 09/25/2003 September YES 4 Needed
SK 09/23/2003 09/25/2003 September YES 4 Needed

The one without the convert returns this:

Absence Code Absence Start Date Absencen Stop Date Absence Month C23 Due Days Over Due Days C23 Completed
SK 10/05/2002 0:00 10/05/2002 0:00 2002 YES 359 Needed
RL 10/08/2002 0:00 10/08/2002 0:00 2002 YES 356 Needed
NC 10/19/2002 0:00 10/19/2002 0:00 2002 YES 345 Needed
SK 10/20/2002 0:00 10/21/2002 0:00 2002 YES 343 Needed
SK 10/28/2002 0:00 10/29/2002 0:00 2002 YES 335 Needed
SK 10/21/2002 0:00 10/29/2002 0:00 2002 YES 335 Needed
SKL 10/01/2002 0:00 10/30/2002 0:00 2002 YES 334 Needed
SKU 11/01/2002 0:00 11/01/2002 0:00 2002 YES 332 Needed
RL 11/06/2002 0:00 11/06/2002 0:00 2002 YES 327 Needed
SK 11/12/2002 0:00 11/12/2002 0:00 2002 YES 321 Needed
NC 11/16/2002 0:00 11/16/2002 0:00 2002 YES 317 Needed
SK 11/18/2002 0:00 11/19/2002 0:00 2002 YES 314 Needed
UA 11/20/2002 0:00 11/20/2002 0:00 2002 YES 313 Needed
UA 11/23/2002 0:00 11/23/2002 0:00 2002 YES 310 Needed
RL 11/26/2002 0:00 11/26/2002 0:00 2002 YES 307 Needed
SK 11/27/2002 0:00 11/27/2002 0:00 2002 YES 306 Needed
SKU 11/26/2002 0:00 11/27/2002 0:00 2002 YES 306 Needed
SK 11/30/2002 0:00 12/01/2002 0:00 2002 YES 302 Needed
UA 12/02/2002 0:00 12/02/2002 0:00 2002 YES 301 Needed
SK 12/02/2002 0:00 12/02/2002 0:00 2002 YES 301 Needed
RL 12/03/2002 0:00 12/03/2002 0:00 2002 YES 300 Needed
SK 12/05/2002 0:00 12/05/2002 0:00 2002 YES 298 Needed
SK 12/06/2002 0:00 12/06/2002 0:00 2002 YES 297 Needed
SK 12/03/2002 0:00 12/06/2002 0:00 2002 YES 297 Needed
RL 12/06/2002 0:00 12/06/2002 0:00 2002 YES 297 Needed
SK 12/02/2002 0:00 12/08/2002 0:00 2002 YES 295 Needed
SK 12/07/2002 0:00 12/09/2002 0:00 2002 YES 294 Needed
RL 12/09/2002 0:00 12/09/2002 0:00 2002 YES 294 Needed
SK 12/10/2002 0:00 12/12/2002 0:00 2002 YES 291 Needed
SK 12/13/2002 0:00 12/14/2002 0:00 2002 YES 289 Needed
SK 12/14/2002 0:00 12/16/2002 0:00 2002 YES 287 Needed
SK 12/13/2002 0:00 12/16/2002 0:00 2002 YES 287 Needed
SK 12/10/2002 0:00 12/17/2002 0:00 2002 YES 286 Needed
RL 12/18/2002 0:00 12/18/2002 0:00 2002 YES 285 Needed
SK 12/18/2002 0:00 12/18/2002 0:00 2002 YES 285 Needed
SK 12/20/2002 0:00 12/20/2002 0:00 2002 YES 283 Needed
SK 12/22/2002 0:00 12/23/2002 0:00 2002 YES 280 Needed
RL 12/23/2002 0:00 12/23/2002 0:00 2002 YES 280 Needed
SKQ 12/28/2002 0:00 12/28/2002 0:00 2002 YES 275 Needed
SK 12/23/2002 0:00 12/30/2002 0:00 2002 YES 273 Needed
SKU 12/29/2002 0:00 12/30/2002 0:00 2002 YES 273 Needed
UA 12/31/2002 0:00 12/31/2002 0:00 2002 YES 272 Needed
SKU 12/18/2002 0:00 12/31/2002 0:00 2002 YES 272 Needed
SK 12/26/2002 0:00 12/31/2002 0:00 2002 YES 272 Needed
SK 12/31/2002 0:00 01/01/2003 0:00 2002 YES 271 Needed
SK 12/31/2002 0:00 01/02/2003 0:00 2002 YES 270 Needed
SK 01/03/2003 0:00 01/03/2003 0:00 2003 YES 269 Needed
NC 01/03/2003 0:00 01/03/2003 0:00 2003 YES 269 Needed
UA 01/03/2003 0:00 01/03/2003 0:00 2003 YES 269 Needed
RL 01/04/2003 0:00 01/04/2003 0:00 2003 YES 268 Needed
SK 01/07/2003 0:00 01/07/2003 0:00 2003 YES 265 Needed
SK 01/05/2003 0:00 01/09/2003 0:00 2003 YES 263 Needed
SK 01/10/2003 0:00 01/10/2003 0:00 2003 YES 262 Needed
SK 01/06/2003 0:00 01/10/2003 0:00 2003 YES 262 Needed
SK 01/12/2003 0:00 01/12/2003 0:00 2003 YES 260 Needed
SK 01/06/2003 0:00 01/15/2003 0:00 2003 YES 257 Needed
UA 01/14/2003 0:00 01/17/2003 0:00 2003 YES 255 Needed
SK 01/16/2003 0:00 01/18/2003 0:00 2003 YES 254 Needed
SK 01/16/2003 0:00 01/18/2003 0:00 2003 YES 254 Needed
RL 01/19/2003 0:00 01/19/2003 0:00 2003 YES 253 Needed
SK 01/20/2003 0:00 01/20/2003 0:00 2003 YES 252 Needed
SK 01/11/2003 0:00 01/21/2003 0:00 2003 YES 251 Needed
SK 01/16/2003 0:00 01/24/2003 0:00 2003 YES 248 Needed
SK 01/20/2003 0:00 01/24/2003 0:00 2003 YES 248 Needed
NC 01/24/2003 0:00 01/24/2003 0:00 2003 YES 248 Needed
RL 01/25/2003 0:00 01/25/2003 0:00 2003 YES 247 Needed
RL 01/26/2003 0:00 01/26/2003 0:00 2003 YES 246 Needed
SK 01/26/2003 0:00 01/27/2003 0:00 2003 YES 245 Needed
SK 01/28/2003 0:00 01/28/2003 0:00 2003 YES 244 Needed
SK 01/29/2003 0:00 01/29/2003 0:00 2003 YES 243 Needed
SK 01/21/2003 0:00 01/29/2003 0:00 2003 YES 243 Needed
SKL 01/16/2003 0:00 01/30/2003 0:00 2003 YES 242 Needed
SK 01/22/2003 0:00 01/31/2003 0:00 2003 YES 241 Needed
SK 01/28/2003 0:00 01/31/2003 0:00 2003 YES 241 Needed
SK 01/28/2003 0:00 01/31/2003 0:00 2003 YES 241 Needed
SK 01/31/2003 0:00 02/01/2003 0:00 2003 YES 240 Needed
SKP 01/15/2003 0:00 02/02/2003 0:00 2003 YES 239 Needed
SKL 01/05/2003 0:00 02/03/2003 0:00 2003 YES 238 Needed
SK 01/31/2003 0:00 02/03/2003 0:00 2003 YES 238 Needed
SK 02/03/2003 0:00 02/04/2003 0:00 2003 YES 237 Needed
SK 02/02/2003 0:00 02/05/2003 0:00 2003 YES 236 Needed
RL 02/08/2003 0:00 02/08/2003 0:00 2003 YES 233 Needed
SK 02/07/2003 0:00 02/12/2003 0:00 2003 YES 229 Needed

As you can see the query without using the convert has the 'Absence Stop Date' descending correctly which also reflects in the 'Days Over Due' field as well.

I think Jeff is correct is saying that I need to format the date field in the presentation layer, ie: Web or Excel.

Thanks for all your help.

GC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources