CODEWITHSUNDEEP
sql-server
user2674855
user2674855

Reputation: 225

converting date time format in sql

i have a stored procedure like this:

ALTER procedure [dbo].[startandenddtime]
@startdate nvarchar(100)
as begin 
declare @date3 nvarchar(100) = cast(CONVERT(varchar(100), @startdate + ' 16:59:59',      120) as datetime)
select date3  as startdate
end

If i pass my startdate as 2013-05-08 i am getting out put as : enter image description here but i want to get out put as 2013-05-08 16:59:59.000..so how i can convert this format is this date time i can store in nvarchar varibale

Upvotes: 2

Views: 3087

Answers (5)

Meldor
Meldor

Reputation: 236

Just don't cast varchar value to datetime

edit: If you don't get what i meant in my comment:

convert(varchar, convert(datetime, @startdate + ' 16:59:59', 120), 120)

Upvotes: 0

Mohammad Arshad Alam
Mohammad Arshad Alam

Reputation: 9862

Try this :

Declare @dt varchar(20);
Declare @startdate varchar(20)='2013-05-08';
SET @dt= CONVERT(VARCHAR(20), CONVERT(datetime,@startdate+' 16:59:59'), 100)
Select @dt;

Upvotes: 0

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11775

Try like this 

 DECLARE @StartDate NVARCHAR(100)='2013-05-08'
 SELECT CONVERT(VARCHAR,@StartDate+'16:59:59', 121)

MORE

SQL Fiddle

Upvotes: 2

Ajay
Ajay

Reputation: 6590

try this

ALTER procedure [dbo].[startandenddtime]
@startdate nvarchar(100)
as begin 
declare @date3 nvarchar(100) = cast(CONVERT(VARCHAR(24),@startdate ,121)) as datetime)
select date3  as startdate
end

For more in details see this, http://technet.microsoft.com/en-us/library/ms187928.aspx

Upvotes: 2

Dhaval
Dhaval

Reputation: 2861

try this

Select cast(CONVERT(varchar(100), @startdate + ' 16:59:59') as date)

Or

Select cast(CONVERT(varchar(100), @startdate + ' 16:59:59') as datetime)

Upvotes: 0

Related Questions