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 |
|
112055
Starting Member
2 Posts |
 Posted - 2002-05-16 : 11:40:24
|
| I created a report that runs from 1st of the Month to today minus 1 day. I have no problem printing the report date out on the result. But instead of 05/01/2002 to 05/07/2002I got 05/01/2002 to 05/7/2002 instead. The leading zero in EndDay day disappear.I have checked my computer system time shortdate is set MM/DD/YYYY.The StartdDay is always 01, but the EnddDay can be single digit day or double digit day. When it is single digit day I would like to keep the leading zero.Here is my SQL:@StartDay = CONVERT(CHAR(2), DATEPART(MM, getDate())) + '/01/' + CONVERT(CHAR(4), DATEPART(YY, getDate())), @EndDay = CONVERT(CHAR(2), DATEPART(MM, getDate())) + '/' + CONVERT(CHAR(2),DATEPART(dd, dateadd(dd,-1,getdate()))) + '/' + CONVERT(CHAR(4), DATEPART(YY, getDate()))Is there anyway to fix this ?Many thanks!!! |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-05-16 : 11:44:52
|
| Since datepart returns an integer, leading zero's will always disappear and need to be manually added like so.@EndDay = RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MM, getDate())), 2) + '/' + RIGHT('0' + CONVERT(CHAR(2),DATEPART(dd, dateadd(dd,-1,getdate()))), 2) + '/' + CONVERT(CHAR(4), DATEPART(YY, getDate())) Jeff BanschbachConsultant, MCDBA |
 |
|
|
dsdeming
479 Posts |
Posted - 2002-05-16 : 11:47:08
|
| Try this:@EndDay = CONVERT(CHAR(2), DATEPART(MM, getDate())) + '/' + RIGHT( '0' + CONVERT(CHAR(2),DATEPART(dd, dateadd(dd,-1,getdate()))), 2 ) + '/' + CONVERT(CHAR(4), DATEPART(YY, getDate())) Whe I require strings of length n, I usually concatenate the string with REPLICATE( padcharacter, n ) at either the front or the back of the string and use LEFT( concatenatedstring, n ) or RIGHT( concatenatedstring, n ) to get the formatted output. |
|
|
|
112055
Starting Member
2 Posts |
Posted - 2002-05-16 : 14:20:32
|
| Hey thank you guys...you are great...it works!!!! |
|
|
|
|
|
|
|
|
leading zero in single digit date