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 |
|
need_some_help
Starting Member
5 Posts |
 Posted - 2001-12-14 : 12:09:08
|
| Hi all,I have a little stored procedure that is inputting a new advert. I need to automatically generate a reference in the following style:AFS14120101 or [AFS][14][12][01][01]The first part is a code depending on which section they've added this job to. Next is the day, then the month, followed by the year. After that is the number of posts today within this section.The only problem is, if today is the 3rd, my use of DATEPART(day, GetDate()) returns 3, and I need it to return 03.I've tried converting it to a char(2), but this doesn't work. Obviously I could do this outside the SP, but i would rather calculate the number of posts using the SP, so I might aswell do the whole thing inside it. In order to turn 3 into 03, do I have to use a user defined function or something?Thanks for your help.P.S. If I was doing this in VB, I would use a simple function:Function ForceFloat(iThis, iLen) Dim iReturn iReturn = iThis Do Until Len(iReturn) = iLen iReturn = "0" & iReturn Loop ForceFloat = iReturnEnd Function |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-14 : 12:24:52
|
| You could concatenate a 0 to the front of it, and then do a RIGHT() statement. In order to concatenate, SQL has to think these are characters and not numbers, otherwise it will try to ADD them.here's a sample:SELECT right(('0' + '3'), 2) -- returns '03'SELECT right(('0' + '13'), 2) -- returns '13'-------------------It's a SQL thing... |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-14 : 13:27:23
|
| REPLACE(CONVERT(varchar, GETDATE(), 3), '/', '') |
|
|
|
need_some_help
Starting Member
5 Posts |
Posted - 2001-12-17 : 04:25:22
|
| Thanks Arnie,a great solution. Cheers. |
|
|
|
|
|
|
|
|
data type conversions