none
datetime in sql server

    Question

  • Hi all,
    i have executed one query on ORACLE Server i got below output.
    select * from openquery(finuat,'select (trunc(sysdate, ''MONTH'') - 1) from dual ')
    output is: 2013-01-31 00:00:00.0000000.
    we are trying but getting wrong  data
    pls provide the Convertion function in sql server
    Friday, February 15, 2013 7:17 AM

Answers

  • That Oracle query gives the last day of preivous month with Datetimeoffset. This can be achieved using DATEADD, DATEDIFF in SQL Server.

     SELECT DATEADD(day,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

    Above query gives the last day of the previous month.

    Then, if you want the timestamp similary to your expected output, we have to use DATETIME2.

    DECLARE @v DATETIME
    SET @v=DATEADD(day,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))  --Get the last day of previous month
    PRINT @v
    SELECT CAST(@v AS DATETIME2) AS YourOutput --Cast as Datetime2 to get your timestamp.

    Output -

    YourOutput
    2013-01-31 00:00:00.0000000
    Hope it helps..

    Narsimha


    Friday, February 15, 2013 11:47 PM

All replies

  • what is your desired output?

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

    Friday, February 15, 2013 4:53 PM
  • That Oracle query gives the last day of preivous month with Datetimeoffset. This can be achieved using DATEADD, DATEDIFF in SQL Server.

     SELECT DATEADD(day,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

    Above query gives the last day of the previous month.

    Then, if you want the timestamp similary to your expected output, we have to use DATETIME2.

    DECLARE @v DATETIME
    SET @v=DATEADD(day,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))  --Get the last day of previous month
    PRINT @v
    SELECT CAST(@v AS DATETIME2) AS YourOutput --Cast as Datetime2 to get your timestamp.

    Output -

    YourOutput
    2013-01-31 00:00:00.0000000
    Hope it helps..

    Narsimha


    Friday, February 15, 2013 11:47 PM