datetime in sql server
-
vendredi 15 février 2013 07:17Hi 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
Toutes les réponses
-
vendredi 15 février 2013 16:53what is your desired output?
Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com
-
vendredi 15 février 2013 23:47
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
- Modifié Naarasimha vendredi 15 février 2013 23:48 spelling mistake
- Proposé comme réponse Piotr Palka dimanche 17 février 2013 15:58
- Marqué comme réponse Eileen ZhaoMicrosoft Contingent Staff, Moderator lundi 25 février 2013 07:21
.png)
