CODEWITHSUNDEEP
sql-server-2008
Aparanjit
Aparanjit

Reputation: 41

How to convert int to date in MS Sql

I have an integer in one of the column as 123114, which I need to convert as DATE format (12/31/2014). How can I do it? I even tried to convert it to datetime and then date, but that is not working either. I am running out with errors. Can anyone help me in this.

Thanks, Rahul

Upvotes: 0

Views: 519

Answers (3)

Jason Whitish
Jason Whitish

Reputation: 1438

Without seeing a query, it's hard to give you a definite answer. Presuming your integers are always in a mmddyy format, this should work:

declare @a int = 121314
declare @b varchar(10)
select @b = cast(@a as varchar(10))

if len(@b) = 6 --mmddyy
begin
select cast(substring(@b,1,2) + '-' + substring(@b,3,2) + '-' +substring(@b,5,2) as date) as castDate --depends on database collation
select convert(date, substring(@b,1,2) + '/' + substring(@b,3,2) + '/' +substring(@b,5,2), 1) as convertedDate
end
else
begin 
    print 'need something more complicated here'
end

You'll definitely want some sort of else condition regardless, to handle bad data, unless your values are pretty tightly constrained.

Upvotes: 0

4Logik
4Logik

Reputation: 177

declare
    @testDate  as int = '123114',
    @StringDate as Char(6) 

set @StringDate = Cast(@testDate as Char(6))

select
    ActualDate = (Substring(@StringDate,1,2) + '/' + Substring(@StringDate,3,2) + '/20' + Substring(@StringDate,5,2))

This would assume that the int was formatted as mmddyy, requiring a leading 0 when m < 10 as mentioned previously.

Upvotes: 0

rushank shah
rushank shah

Reputation: 856

declare @yourdate int
select @yourdate = 20141231
select CONVERT (date,convert(char(8),@yourdate))

try this..

Upvotes: 0

Related Questions