July 17, 2012 at 5:27 am
Hi Guys,
Sorry if this post is on the wrong "section", i'm converting a mantisBT installation and i need to convert a datetime column to a int colomun with the specific aspect DDMMYYYY 00:00, and when i convert through the mantisBT upgrade only migrate de DDMMYYYY and the rest is set to 00:00, i want to know if exists any query in SQL that i can made to do this conversion, i'v tried this but with no success:
select DateField, convert(int, DateField) from #Dates
GO
alter table #Dates add NewDateField INT
GO
update #Dates set NewDateField = convert(int, DateField)
select * from #Dates
GO
alter table #Dates DROP COLUMN DateField
GO
select * from #Dates
thanks for the help
July 17, 2012 at 6:15 am
one of the worst practices is converting a datetime any other data type. Let alone to integer format DDMMYYYY !!!
It just looses all date characteristics and functions one can use with a datetime data type;
In some cases, converting to integer in the format YYYYMMDD is suggested/advised for certain purposes, but that is the only acceptable exceptance.
Well, if you insist on converting to DDMMYYYY this is how you can do it.
select convert(int, replace(convert(char(10), getdate(),103), '/','') )
If you insist on having the 0000 on the right side, and INT datatyped column will not do.
( have a look at BOL topic "int, bigint, smallint, and tinyint (Transact-SQL) " )
It will have to be a bigint or a decimal(12,0)
select convert(int, replace(convert(char(10), getdate(), 103), '/', '')) DDMMYYYY
, convert(bigint, replace(convert(char(10), getdate(), 103), '/', '') + '0000') DDMMYYYY0000
, convert(decimal(12, 0), replace(convert(char(10), getdate(), 103), '/', '') + '0000') DDMMYYYY0000_dec12
Best is to stay with the datetime datatype ! and enjoy all the fruits that come with it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 17, 2012 at 10:53 am
Hi johan,
I know that you are right, but the MantisBT force me to convert the "datetime" columns to "int", its the only way to perform a upgrade to a newer version...
I'll try those solutions, thanks for helping..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply