SQL function that applies to each column row

Question

SQL function that applies to each column row

Navigation

#1 by (5 votes)
#2 by (2 votes)

5

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

example:

select *, separadata(REP_DATA_INICIO) from intranet_reportmensal

The function would apply to each line of this 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

sql

asked by anonymous 15.12.2014 / 20:03

2 answers

5

SQL SERVER

Use the DATEPART ()

If the Date field in your table is minhaData you can use a SELECT of this type 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 DATEPART (datePart, date) function gets two parameters:

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

MySQL

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

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

15.12.2014 / 20:19

Identify installed extension in google chrome How to serialize an exception with closure

score 2 · Accepted Answer

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

I suggest working with date type and specific 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 cosum it:

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

See it working: link

"Cross apply" credits: link