SQL function that applies to each row of the column

Question

We work with several tables that contain a date field, sometimes we need to separate the date string into 3 columns (day/month/year).
I made a function where you pass the date string and it returns these 3 columns, but how do I return them within another Query?

example:

select *, separadata(REP_DATA_INICIO) from intranet_reportmensal 

The function would apply to each row of that query.

the function is this

FUNCTION [dbo].[separadata](@data varchar(10))
 returns TABLE 
 AS RETURN SELECT Parsename(Replace(@data, '/', '.'), 3) as DIA, Parsename(Replace(@data, '/', '.'), 2) as MES, Parsename(Replace(@data, '/', '.'), 1)  as ANO

Answer 1

SQL SERVER

Use the function DATEPART()

If the field Date of your table for minhaData can use such a SELECT to separate that date into day/month/year.:

SELECT DATEPART(yyyy,minhaData) AS ano,
       DATEPART(mm,minhaData) AS mes,
       DATEPART(dd,minhaData) AS dia
FROM minhaTabela

The function DATEPART(datePart, date) receives two parameters:

• datepart - indicates part of date to extract and may be one of the following options: year: yyyy,yy month: mm,m day: dd,d (more options)
• date - an expression representing a type Date: team, date, smalldatetime, datetime, datetime2, or datetimeoffset, can be a column of a table, a variable or a string.

---

Mysql

Use the functions YEAR(date), MONTH(date) and DAY(date)

SELECT YEAR(minhaData) AS ano,
       MONTH(minhaData) AS mes,
       DAY(minhaData) AS dia
FROM minhaTabela

Answer 2

In Microsoft SQL Server, you can join with the result of a function whose return is of the type TABLE (like your).

I suggest working with specific date type and functions, as recommended by @ramaral. The function would look like this:

create FUNCTION dbo.separadata(@data datetime)
 returns TABLE 
 AS RETURN 
SELECT DATEPART(yyyy,@data) AS ano,
DATEPART(mm,@data) AS mes,
DATEPART(dd,@data) AS dia

And to sew it:

select * from Tabela
cross apply separadata(Tabela.data)

See working: http://www.sqlfiddle.com/#! 6/b287d/14/0

_{"Cross apply" credits: https://stackoverflow.com/a/699362/1274092}