TO_DATE para dentro do Sql Sever

27/04/2012

Como eu insiro a linha abaixo feita para Oracle inserir dentro de um SQL Server?

INSERT INTO FRAUDES (DAT_FRA, NUM_BIN, EMI_N_CODIGO, AMT_1, TYP_FRA, COD_GRP_PRODUTO, PT_SRV_ENTRY_MDE) VALUES (TO_DATE (02/20/2012, MM/DD/YYYY), 459080, 0, 20000.01, 029, VC, 051);

Pjava

Curtir t�pico + 0

Responder

Posts

27/04/2012

Joel Rodrigues

D� uma olhada nessa documenta��o oficial: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Responder

Gostei + 0

02/05/2012

Eduardo Pereira

INSERT INTO FRAUDES 
(
    DAT_FRA, NUM_BIN, EMI_N_CODIGO, AMT_1, TYP_FRA, COD_GRP_PRODUTO, PT_SRV_ENTRY_MDE
)
VALUES 
(
    CONVERT(datetime,02/20/2012,103),
    459080,
    0,
    20000.01,
    029,
    VC,
    051
);

Os tipos de data s�o:

101 -> MM/dd/yyyy
102 -> yyyy.MM.dd
103 -> dd/MM/yyyy
104 -> dd.MM.yyyy
105 -> dd-MM-yyyy

*Para String utilize aspas simples

Responder

Gostei + 0

03/05/2012

Jair N.

Boa Tarde n�o esque�a ao passar o par�metro na sua consulta por dbo.TO_CHAR(...

CREATE FUNCTION TO_CHAR(@Imput SQL_VARIANT, @Format VARCHAR(55))
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @Result VARCHAR(255)

SET @Format = UPPER(@Format)

IF (ISDATE(CAST(@Imput AS VARCHAR)) = 1)
BEGIN -- 00 0 Nihill
IF (@Format = MM/DD/YYYY)
SET @Result = CONVERT(CHAR(10), @Imput, 101)
ELSE -- 02 102 ANSI
IF (@Format = YY.MM.DD )
SET @Result = CONVERT(CHAR(08), @Imput, 102)
ELSE -- 03 103 British/French
IF (@Format = DD/MM/YYYY )
SET @Result = CONVERT(CHAR(10), @Imput, 103)
ELSE -- 04 104 German
IF (@Format = DD.MM.YY )
SET @Result = CONVERT(CHAR(08), @Imput, 104)
ELSE -- 05 105 Italian
IF (@Format = DD-MM-YY )
SET @Result = CONVERT(CHAR(08), @Imput, 105)
ELSE -- 08 108 Hour Minute Seg
IF (@Format = HH:MI:SS )
OR (@Format = HH:MM:SS )
SET @Result = CONVERT(CHAR(08), @Imput, 108)
ELSE -- 10 110 USA
IF (@Format = MM-DD-YY )
SET @Result = CONVERT(CHAR(08), @Imput, 110)
ELSE -- 11 111 JAPAN
IF (@Format = YY/MM/DD )
SET @Result = CONVERT(CHAR(08), @Imput, 111)
ELSE -- 12 112 ISO YYMMDD
IF (@Format = YYYYMMDD )
SET @Result = CONVERT(CHAR(08), @Imput, 112)
ELSE -- 14 114 ODBC
IF (@Format = HH:MI:SS:MMM)
OR (@Format = HH:MM:SS:MMM)
SET @Result = CONVERT(CHAR(12), @Imput, 114)
ELSE -- 20 120 ODBC canonical
IF (@Format = YYYY-MM-DD HH:MI:SS)
OR (@Format = YYYY-MM-DD HH:MM:SS)
SET @Result = CONVERT(CHAR(20), @Imput, 120)
ELSE -- 21 121 ODBC canonical (with milliseconds)
IF (@Format = YYYY-MM-DD HH:MI:SS.MMM)
OR (@Format = YYYY-MM-DD HH:MM:SS.MMM)
SET @Result = CONVERT(CHAR(24), @Imput, 121)
ELSE --XX 0 Default
SET @Result = CONVERT(CHAR(10), @Imput, 120)
END
ELSE
IF (ISNUMERIC(CAST(@Imput AS VARCHAR)) = 1)
BEGIN
IF (@Format = )
SET @Result = CONVERT(VARCHAR(255), @Imput)
ELSE
SET @Result = LTRIM(STR(CAST(@Imput AS FLOAT), 255, LEN(@Format) - CHARINDEX(., @Format)))
END

RETURN @Result

END
GO

Responder

Gostei + 0