Formatting a date in a stored procedure
-
I really want to thank everyone for the help you have provided. It's really helping me in my understanding of SQLServer. I have a new challenge that may not be a challenge for any of you. In my stored procedure I need to display the date in the following ways.
Format the date to show the current month as 05
Format the date to show the current day as 09
Format the date to show the current year as 06
How can this be done?
-
declare @TheMonth int
select @TheMonth = DATEPART(mm, getdate())
select
case len(@TheMonth)
when 1 then
'0' + cast(@TheMonth) as char(1))
else
cast(@TheMonth as char(2))
end as StrMonth
You could also roll the length check into a function if you wanted.
-
This is fantastic!!! I'm going to try and employ this. But I hope you can help me just a bit. Here is my stored procedure. How can I use what you suggest?
CREATE PROCEDURE sp_CreateTextFile
As
Select CAST('AA' AS Varchar(50)) + CAST(ORG AS Varchar(50)) + CAST('00' AS Varchar(50)) + CAST(Acct_Nbr AS Varchar(50)) + CAST(Tran_Code AS Varchar(50)) + CAST(Amount AS Varchar(50)) AS MyAACol,
Datepart(mm, Eff_Date) AS Months,
DatePart(d, Eff_Date) AS Days,
DatePart(yy, Eff_Date) AS Years,
CASE WHEN Tran_Code > ' ' THEN '00000' END ZeroSpacer,
CASE WHEN Amount > 0 Then 'AM' END AA_LB_Nbr_Flag,
Lockbox_nbr,
CAST(' ' AS Varchar(50)) AS Filler2,
CAST('RMCH000M' as Varchar(50)) AS Source
From tblUploadFile
Order By Lockbox_Nbr,Tran_Code
GO
-
CREATE PROCEDURE sp_CreateTextFile
As
Select
CAST('AA' AS Varchar(50)) + CAST(ORG AS Varchar(50)) + CAST('00' AS Varchar(50)) + CAST(Acct_Nbr AS Varchar(50)) + CAST(Tran_Code AS Varchar(50)) + CAST(Amount AS Varchar(50)) AS MyAACol,
case len(Datepart(mm, Eff_Date))
when 1 then
'0' + cast(DATEPART(mm, Eff_Date) as char(1))
else
cast(DATEPART(mm, Eff_Date) as char(2))
end AS Months,
case len(Datepart(dd, Eff_Date))
when 1 then
'0' + cast(DATEPART(dd, Eff_Date) as char(1))
else
cast(DATEPART(dd, Eff_Date) as char(2))
end AS Days,
case len(Datepart(yy, Eff_Date))
when 1 then
'0' + cast(DATEPART(yy, Eff_Date) as char(1))
else
cast(DATEPART(yy, Eff_Date) as char(2))
end AS Years,
CASE WHEN Tran_Code > ' ' THEN '00000' END ZeroSpacer,
CASE WHEN Amount > 0 Then 'AM' END AA_LB_Nbr_Flag,
Lockbox_nbr,
CAST(' ' AS Varchar(50)) AS Filler2,
CAST('RMCH000M' as Varchar(50)) AS Source
From
tblUploadFile
Order By Lockbox_Nbr,Tran_Code
GO
As you can see, the case statement is identical so it could potentially be rolled into a function.
A couple questions for you:
Is there a particular reason you're doing a cast on a literal? From what I can see it doesn't look necessary.
What are you trying to get with your various CASE statements? (such as: CASE WHEN Tran_Code > ' ' THEN '00000' END ) Generally a Case statement is for the possibility of multiple values.
-
Adjulla, I really want to thank you for your help. This is just what I need. Regarding how why I'm using cast, I was getting a conversion error when trying to post to a table without using cast. With the case select I'm trying to create a variable for posting to the database.
-
Hmm. I'd be interested to see your whole process. At minimum, your first part could be trimmed to:
'AA' + CAST(ORG AS Varchar(50)) + '00' + CAST(Acct_Nbr AS Varchar(50)) + CAST(Tran_Code AS Varchar(50)) + CAST(Amount AS Varchar(50)) AS MyAACol
To clean it up a little.
I'm also not sure what you mean by "create a variable" regarding the CASE statements. By having what you have, you're either going to get the literal value or NULL (which is generally a bad thing) for each of those columns.
-
Try:
SELECT 'AA'+CONVERT(varchar,ISNULL(ORG,''))+'00'
+CONVERT(varchar,ISNULL(Acct_Nbr,''))
+ISNULL(Tran_Code,'')
+CONVERT(varchar,ISNULL(Amount,'')) AS MyAACol
, LEFT(CONVERT(varchar,Eff_Date,1),2) AS Months
, RIGHT(CONVERT(varchar,Eff_Date,12),2) AS Days
, LEFT(CONVERT(varchar,Eff_Date,12),2) AS Years
, CASE WHEN Tran_Code > ' ' THEN '00000' ELSE NULL END AS ZeroSpacer
, CASE WHEN Amount > 0 Then 'AM' ELSE NULL END AS AA_LB_Nbr_Flag
, Lockbox_nbr
, '' AS Filler2
, 'RMCH000M' AS Source
FROM tblUploadFile
ORDER BY Lockbox_Nbr, Tran_Code
The ISNULL(,'') was added to insure that if any of these columns are NULL that the concatenation still occurs. You could change the '' portion to contain an alternative value for NULL. Or if the columns do not allow NULL, this addition could be removed.
The ELSE NULL was added to the CASE statements to show the alternative, instead of just implying it. Mainly to clarify the intended results.
Does the Tran_Code column allow a single space character? If not try LEN(Tran_Code) > 0 as a better expression.
The ORG, Acct_Nbr, and Amount columns are assumed to be a non character data type, otherwise treat them like Tran_Code.
Andy
-
I didn't test this for performance, but there is a way how to write the code more simple - instead of CASE, like in Pam's example, you can just concatenate and then cut the string to needed number of characters.
declare @TheMonth int
select @TheMonth = DATEPART(mm, getdate())
select RIGHT('00' + CAST(@TheMonth as varchar(2)),2) as StrMonth
Also, I would prefer CONVERT to CAST, because it allows even nicer way of reaching the required result:
select CONVERT(varchar(10), getdate(),112) /*whole date*/
select SUBSTRING(CONVERT(varchar(10), getdate(),112),3,2) /*two digit year*/
select SUBSTRING(CONVERT(varchar(10), getdate(),112),5,2) /*two digit month*/
select SUBSTRING(CONVERT(varchar(10), getdate(),112),7,2) /*two digit day*/
In your query you could then use this SQL, which is a lot easier to understand and manage:
Select
CAST('AA' AS Varchar(50)) + CAST(ORG AS Varchar(50)) + CAST('00' AS Varchar(50)) + CAST(Acct_Nbr AS Varchar(50)) + CAST(Tran_Code AS Varchar(50)) + CAST(Amount AS Varchar(50)) AS MyAACol,
SUBSTRING(CONVERT(varchar(10), Eff_Date,112),5,2) AS Months,
SUBSTRING(CONVERT(varchar(10), Eff_Date,112),7,2) AS Days,
SUBSTRING(CONVERT(varchar(10), Eff_Date,112),3,2) AS Years,
CASE WHEN Tran_Code > ' ' THEN '00000' END ZeroSpacer,
CASE WHEN Amount > 0 Then 'AM' END AA_LB_Nbr_Flag,
Lockbox_nbr,
CAST(' ' AS Varchar(50)) AS Filler2,
CAST('RMCH000M' as Varchar(50)) AS Source
From
tblUploadFile
Order By Lockbox_Nbr,Tran_Code
HTH, Vladan
-
Oh, BTW, David, I don't think it is a good idea to use CONVERT and not specify the length of result data type (just VARCHAR, instead of e.g. VARCHAR(50)). As far as I know, it is regarded as bad practice, because you can't be sure what the result will be... in many situations it is not a problem, but then suddenly you start getting an error - and it is really hard to troubleshoot it and find the source.
-
I believe that if you do not specify a length after a varchar it will default to 30. So, as Vladan stated above, make sure you specify the length of the varchar.
-
If you are stating that the use of CONVERT(varchar,ISNULL(Acct_Nbr,'')) without specifying the varchar(<length>
is bad, I agree, however given no DDL for the table this seemed a better alternative than a wild guess.Otherwise since I am using a predefined portion of the string and specifying the format, use if the implied 30 value by not speficying is just my lazyness and will never error.
Andy
-
Oh, every time we fall for the trap of relying on implicit defaults, it will error - sooner or later.
As for 30...? Nope... Doesn't seem like it in my case anyway
select @@version
go
declare @v-2 varchar
set @v-2 = 'aaa'
select len(@v), datalength(@v), @v-2
go
---------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
----------- ----------- ----
1 1 a
...29 short of 30..
/Kenneth
-
That's true... one more reason to stick to precise definition.
From BOL :
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
-
I like "simple" when I can get away with it
... here's my take on the date part thingy...--===== Create a sample date
DECLARE @somedate DATETIME
SET @SomeDate = '05/02/2006'
--===== SELECT the 2 digit date parts with leading zeros as required
SELECT CONVERT(CHAR(2),@SomeDate,1) AS Months,
CONVERT(CHAR(2),@SomeDate,3) AS Days,
CONVERT(CHAR(2),@SomeDate,2) AS Years
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It works fine, however do try out the difference when the language changes.
set language british
set language us_english
...just wanted to point out that it's important to provide dates in a 'failsafe' format.
SET @Somedate = '20060502' will do the trick.
/Kenneth
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply