change date to month and month to date in datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

change date to month and month to date in datetime

Author

Topic

Phany
Starting Member

8 Posts

Posted - 2014-01-29 : 01:22:13

Hi All,

does anyone know to change 2014-01-13 05:59:00.000 to 2014-13-01 05:59:00.000?

I tried this
left(convert(varchar(26),[CloseTime],112),4)+'-'+RIGHT(convert(varchar(8),[CloseTime],112),2)+
'-'+RIGHT(convert(varchar(8),[CloseTime],112),4)+'-'+SUBSTRING(convert(varchar(26),[CloseTime],120),10,10)

and the result is 2014-13-0113-3 05:59:00, from this how do i remove only 13-3 the bold numbers

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-01-29 : 02:19:04

Hi,

You are in a 2012's sql forum, so there is a function :FORMAT

SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd hh:mm:ss.ffffffftt')
SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss')

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-01-29 : 02:29:18

[code]

declare @CloseTime as datetime

set @CloseTime='2014-01-13 05:59:00.000'

select left(convert(varchar(26),@CloseTime,112),4)+'-'+RIGHT(convert(varchar(8),@CloseTime,112),2)+
'-'+SUBSTRING(convert(varchar(8),@CloseTime,112),5,2)
+' ' +SUBSTRING(convert(varchar(30),@CloseTime,120),12,10)

[/code]

edited:
[code]

;With aCTE
AS (
SELECT Cast('2014-01-13 05:59:00.000' as datetime) AS [CloseTime]
)
select left(convert(varchar(26),[CloseTime],112),4)+'-'+RIGHT(convert(varchar(8),[CloseTime],112),2)+
'-'+SUBSTRING(convert(varchar(8),[CloseTime],112),5,2)
+' ' +SUBSTRING(convert(varchar(30),[CloseTime],120),12,10)

from aCTE

[/code]

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

Phany
Starting Member

8 Posts

Posted - 2014-01-29 : 03:23:55

Thank you Stepson.... will try and keep you posted :-)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-29 : 03:56:15

[code]
declare @str varchar(100) = '2014-01-13 05:59:00.000'

select stuff(stuff(@str, 6, 2, substring(@str, 9, 2)), 9, 2, substring(@str, 2, 2))
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Phany
Starting Member

8 Posts

Posted - 2014-01-29 : 12:18:50

I have one more to solve

I need to calculate Case age, which requires to calculate number days from current date till opendatetime ='2013-01-5 20:53:27.000'

when i try getdate()-opendatetime, it results 1900-08-07 04:07:02.000

it is supposed to be 13.13

help please!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-29 : 22:00:10

use datediff(). You want the age in terms of Month ?

quote:
calculate number days from current date till opendatetime ='2013-01-5 20:53:27.000'

datediff(month, getdate(), opendatetime)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources