Friday, February 15, 2013 7:17 AMHi 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 4:53 PMwhat is your desired output?
Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com
Friday, February 15, 2013 11:47 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.
YourOutput 2013-01-31 00:00:00.0000000Hope it helps..