| Author |
Topic |
|
domap
Starting Member
7 Posts |
 Posted - 2012-01-31 : 11:30:12
|
| Good Morning,I creating an export file. Within that file, I have it calculating the Day of the Year, but I need to pad the result with zeroes if it is less than 3 characters. (SELECT CONVERT(VARCHAR, datepart(dayofyear,'#RunDate#')))I am looking to have the script pad the dayofyear with zeros as follows:1) IF the DayofYear = 2, then PAD it with 1 Zero.2) IF the DayofYear = 1, then PAD it with 2 Zeroes.So, if the Day of Year = 30, then it should come out as 030. If the Day of Year = 9, then it should come out with 009.Thanks in advance for the help! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-31 : 11:40:14
|
| [code](SELECT RIGHT('00'+CONVERT(VARCHAR(3), datepart(dayofyear,'#RunDate#')),3)) [/code] |
 |
|
|
domap
Starting Member
7 Posts |
Posted - 2012-01-31 : 11:57:15
|
| Unfortunately, when I try that, I receive an error..."Incorrect syntax near the keyword: Select". |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-31 : 12:14:49
|
| Couple of questions:Does your original query --> (SELECT CONVERT(VARCHAR, datepart(dayofyear,'#RunDate#'))) <-- run without any errors?Where are you running this? From a client program?Are you using Microsoft SQL Server as your DBMS? |
|
|
|
domap
Starting Member
7 Posts |
Posted - 2012-01-31 : 12:18:23
|
| Did some further testing. I am using MS SQL Server as my DBMS.The original query executes without error from both the Client Program and via SQL Query.When I run the updated query through SQL Query backend...works without error. When I run it through the Client Program, I receive the syntax error. |
|
|
|
domap
Starting Member
7 Posts |
Posted - 2012-01-31 : 12:31:54
|
| I picked it apart line by line and row by row. It is working perfectly now.THANK YOU THANK YOU THANK YOU for the help! Very much appreciated! |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-31 : 12:32:28
|
| That seems like the client program is somehow mangling the query. Are you able to run profiler? If so you can see what gets sent to the server from the client program, and that might give us a clue. Nothing else comes to mind right now.... |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-31 : 12:33:28
|
| Awesome... Strike what I said earlier about profiler. |
|
|
|
domap
Starting Member
7 Posts |
Posted - 2012-01-31 : 12:37:13
|
| Yup...good old PEBKAC! |
|
|
|
|
Re: Padding Result with Zeroes