Date conversion

Question

Hello,

Please let me know how can i convert 20130102 in this format 01/02/213  MM/DD/YYYY. please note 20130102 is int in my table column .

Answer 1

Declare @Table As Table
(
Coli Int
)
Insert @Table
Select 20130102 ;


Select *,Convert(Varchar(10),Convert(Datetime,str(Coli)),101) From @Table

---

Please have look on the comment

Answer 2

 select CONVERT(VARCHAR(14), convert(DATE,cast(20130102 as varchar) ,103),101)

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 3

Try this

declare @dt varchar(10)='20130102'
select convert(varchar(10),convert(datetime,@dt),101)

Regards
Satheesh

Answer 4

i recommend you create a function that does that conversion:

CREATE FUNCTION [dbo].[ConvDateKey]
(
	@Key INT
)
RETURNS VARCHAR(10)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT 
AS
BEGIN

	RETURN CONVERT(VARCHAR(10), CONVERT(DATE, @Key, 112), 101);

END

SELECT [dbo].[ConvDateKey](20120417)

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Answer 5

How many different answers here!

declare @date int = 20130102;

select CONVERT(VARCHAR(14), convert(DATE,cast(20130102 as varchar) ,103),101) -- why 14 (var)chars?
select CONVERT(VARCHAR(14), convert(DATE,cast(20130102 as varchar) ,101),101)
select CONVERT(VARCHAR(14), convert(DATE,cast(20130102 as varchar) ,112),101)

-- The "style" (103,101,112) are necessary from date to string (see link 1),
-- here 103 and 101 are simply ignored because '20130102' is recognized as ISO? (I think 103 and 112 are both still wrong, or not?)

-- I think '20130102' to date requires ISO format (112), then from Date to text require USA format (101)
select CONVERT(CHAR(10), convert(DATE, cast(20130102 as char(8)), 112), 101)

-- this one works but... STR... (removing blanks are extra work or can cause problem?)
Select Convert(Varchar(10),Convert(Datetime,str(@date)),101)
-- STR function (see Link 2)
-- with SQL 2008 R2 there is date (!)
Select Convert(char(10), Convert(date, str(@date, 8)), 101)  -- THIS ONE IS MY PREFERENCE 

-- Note. Datetime/Date formats are influenced by culture of the server (and by time zone),
-- keep this in mind when there is a deploy on server that give some strange error
-----------------------------------------------------------------------------------------

-- another way (simple and secure), absolutely indipendent of culture
declare @s char(8) = str(@date,8); -- default for str is 10, here is a problem !!
select substring(@s, 5, 2) + '/' + substring(@s, 7, 2) + '/' + substring(@s, 1, 4)

Link 1: Cast & Convert

Link 2: STR