To Create a Function to split dates into Year, Month, Date into a separate column in SQL
To Create a Function to split dates into Year, Month, Date into a separate column in SQL
Trying to create a function to split dateformat of "2018-05-21" to 2018 | 05 | 21 | as three separate columns. Tried creating the function as below but gives me error on "month", "Day". Error says "incorrect syntax near 'month'. Expecting '(' or Select."
CREATE FUNCTION [dbo].[functionname]
(
-- Add the parameters for the function here
@DateFormat AS DATETIME
)
RETURNS VARCHAR (MAX)
AS
BEGIN
RETURN DATEPART(YEAR,@DateFormat),
DATEPART(Month,@DateFormat),
DATEPART(Day,@DateFormat)
END
GO
So that others can also call it, when they want to use it directly on any kind of analysis o have a better format to use.
– Tamil
Sep 10 '18 at 18:20
I did try this on table directly. It does work. /* DATEPART(year,Date) as year, DATEPART(month,Date) as Month, DATEPART(DD,Date)as Date */
– Tamil
Sep 10 '18 at 18:22
If you already have the date formatted with - you can do a replace(date, '-', '|')
– Brad
Sep 10 '18 at 18:26
Probably not in the way you want, that can be used on individual rows in a select query... That cross apply answer might get the job done, but anyone who can write an APPLY statement also knows how to call MONTH() if they need it.
– Joel Coehoorn
Sep 10 '18 at 18:42
1 Answer
1
The problem with your current SQL is that a scalar only returns a single value. You need to use a table value function to get multiple columns.
This is a TVF version which will provide three columns
CREATE FUNCTION [dbo].[FunctionName]
(
@DateFormat AS DATETIME
)
RETURNS TABLE AS RETURN
(
SELECT DATEPART(YEAR,@DateFormat) AS [Year],
DATEPART(Month,@DateFormat) AS [Month],
DATEPART(Day,@DateFormat) AS [Day]
)
Example usage:
DECLARE @dates TABLE (SomeDate DATE)
INSERT INTO @dates SELECT '01/25/2018'
INSERT INTO @dates SELECT '10/01/2008'
SELECT d.*,fn.* FROM @dates d
CROSS APPLY [dbo].[FunctionName](d.SomeDate) fn
And some documentation.
That said, I personally don't like this implementation. I would simply expect the DATEPART statements in the SELECT portion of the SQL. I think the TVF makes it more complicated and doesn't provide any tangible benefits.
Beat me to it! But there it is. <Clearing my clipboard>
– Eric Brandt
Sep 10 '18 at 18:35
Perfect! This worked. I have a question. If is select certain columns to be viewed and apply TVF, i dont see the o/p except for the selected columns in the select statement. For example, if i give SELECT Column1,Column2, Column3 from [Tablename] CROSS APPLY [dbo].[FunctionName](columnname)
– Tamil
Sep 10 '18 at 19:06
pardon me for many questions. I am working on learning...
– Tamil
Sep 10 '18 at 19:09
@Tamil Add an alias to the function call and you can specify the alias.column field you want. I've updated my example to show this.
– UnhandledExcepSean
Sep 10 '18 at 19:41
@UnhandledExcepSean Thank you!! Works perfect!!
– Tamil
Sep 10 '18 at 20:05
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Why do you want to create a function for this?
– Vishnu Kunchur
Sep 10 '18 at 18:19