May 5, 2010 at 10:32 pm
Hi experts,
In sql server 2008, function called SYSDATETIMEOFFSET()
output: 2010-05-06 04:26:35.5570000 +05:30
how we can get the same in SQL 2005. I tried through following t_sql
DECLARE @local_time DATETIME;
DECLARE @gmt_time DATETIME;
SET @local_time = getdate();
SET @gmt_time = getutcdate();
SELECT convert(varchar(40),getutcdate(),126)+
--'Server time zone: '
+ CASE
WHEN DATEDIFF(minute,@gmt_time,@local_time)>= 0 THEN
'+'
ELSE
'-'
END
+ RIGHT(CONVERT(VARCHAR(40),
100+ABS(DATEDIFF(minute,@gmt_time,@local_time)/60)),
2)
+ ':'
+ RIGHT(CONVERT(VARCHAR(40),
100+ABS(DATEDIFF(minute,@gmt_time,@local_time)%60)),
2);
GO
output: 2010-05-06T04:26:35.677+05:30
However it is returning only 3digits as milliseconds. I need it 6digits
any help would be greatly appreciated.
Cheers,
Asan
May 6, 2010 at 6:58 am
Hi Rookie...
I didnt modified the entire code but just added ur requirement verify it once and if u want it in any other manner then tell me i will try out in another way.......
DECLARE @local_time datetime;
DECLARE @gmt_time datetime;
SET @local_time = getdate();
SET @gmt_time = getutcdate();
SELECT SUBSTRING(CONVERT(varchar(40),getutcdate(),126),1,CHARINDEX('.',CONVERT(varchar(40),getutcdate(),126),1)-1)
+'.'+ LEFT(CONVERT(nvarchar(10),DATEPART(ms,getutcdate()))+'0000',6)
--'Server time zone: '
+ CASE
WHEN DATEDIFF(minute,@gmt_time,@local_time)>= 0 THEN
'+'
ELSE
'-'
END
+ RIGHT(CONVERT(VARCHAR(40),
100+ABS(DATEDIFF(minute,@gmt_time,@local_time)/60)),
2)
+ ':'
+ RIGHT(CONVERT(VARCHAR(40),
100+ABS(DATEDIFF(minute,@gmt_time,@local_time)%60)),
2);
GO
:-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-)
May 6, 2010 at 7:54 am
The DATETIME datatype only has 3 digits, so you cannot get 6 digits.
May 6, 2010 at 9:28 pm
thanks michael.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply