I have a number as follows: 13901210 I want the output to give the following result 1390/12/10
Input function: a number of bigint Output: a string
How do I create this function?
Add a comment
|
5 Answers
I wonder why no one suggested this Simple Query
declare @input bigint=13901210
select REPLACE(Convert(varchar,Convert(date,CONVERT(varchar,@input))),'-','/')
1 Comment
Dave Cousineau
will this depend on culture info?
DECLARE @integer int = 13901210;
DECLARE @part1 int = @integer / 10000;
DECLARE @part2 int = (@integer - @integer / 10000 * 10000) / 100;
DECLARE @part3 int = @integer - @integer / 100 * 100;
DECLARE @result char(10) =
CAST(@part1 AS char(4)) + '/'
+ CAST(@part2 AS char(2)) + '/'
+ CAST(@part3 AS char(2))
SELECT Part1 = @part1, Part2 = @part2, Part3 = @part3, Result = @result;
GO
Comments
You can try this query:
SELECT SUBSTRING(CAST(13901210 AS varchar(8)), 1,4) + '/' + SUBSTRING(CAST(13901210 AS varchar(8)), 5,2) + '/' + SUBSTRING(CAST(13901210 AS varchar(8)), 7,2)
Just replace the value with whatever field/variable you have with the number stored
The full function would look like this:
CREATE FUNCTION [dbo].[int_to_slashed_string] ( @value int )
RETURNS varchar(10)
AS
BEGIN
DECLARE @string varchar(8);
SET @string = CAST(@value AS varchar(8));
RETURN SUBSTRING(@string, 1,4)
+ '/' + SUBSTRING(@string, 5,2)
+ '/' + SUBSTRING(@string, 7,2);
END
This function assumes the value contains 8 digits. If there are other limitations or conditions, the function will have to be modified.
Comments
If you do not want to substring the value then you can do this:
DECLARE @nbr INT=13901210
SELECT
LEFT(@nbr,4)+'/'+
SUBSTRING(CAST(@nbr AS VARCHAR(10)),5,2)+'/'+
RIGHT(@nbr,2)
And then the function would look like this:
CREATE FUNCTION [dbo].[GetDateFormatForInt] (@nbr int)
RETURNS varchar(10)
AS
BEGIN
RETURN
LEFT(@nbr,4)+'/'+
SUBSTRING(CAST(@nbr AS VARCHAR(10)),5,2)+'/'+
RIGHT(@nbr,2);
END
This will only work if is a 10 digit number
Comments
This also works for me:
CREATE PROCEDURE getdatestr
@result nvarchar(12) output,
@info bigint
AS
BEGIN
SET @result=(SELECT LEFT(CAST(@info AS VARCHAR(8)),4)+'/'+
SUBSTRING(CAST(@info AS VARCHAR(8)),5,2)+'/'+
RIGHT(CAST(@info AS VARCHAR(8)),2))
RETURN @result
END
GO
Except that LEFT,RIGHT return nvarchar and SUBSTRING returns string :) Anyways try it...it gives the same output and if you are ok with it then it is simple to get.
