Click here to Skip to main content
12,405,700 members (62,996 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
Hello,


declare @date varchar(10)
declare @d datetime
declare @hour varchar(2)
declare @minute varchar(2)
declare @final varchar(20)
set @date='10/14/2014'
set @hour='16'
set @minute='03'
set @final=(@date+' '+@hour+':'+@minute+':00:000')
set @d =CONVERT(DATETIME, @final)
print @final
print @d


it showing error to convert

plz help

Thanks
srishti
Posted 21-Aug-14 2:17am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Hi, you should really be specifying the date format you are using when converting to date type.

If you add the following line before your variable declaration section:

SET DATEFORMAT MDY

Then have a read over the msdn documentation for SET DATEFORMAT (Transact-SQL) statement.

Hope it helps.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Change your script to this
declare @date varchar(10)
 declare @d datetime
 declare @hour varchar(2)
 declare @minute varchar(2)
 declare @final varchar(20)
 set @date='10/14/2014'
 set @hour='16'
 set @minute='03'
 set @final=(@date+' '+@hour+':'+@minute+':00')
 set @d =CONVERT(DATETIME, @final)
 print @final
 print @d

My only question is why are you doing date manipulation with VARCHAR?
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

you can try something like this,

--Date
declare @date varchar(10)
declare @d DATE
set @date='10/14/2014'
set @d = CONVERT(DATE, @date, 101)	--101 the formate mm/dd/yyyy
--print @d

--Time
declare @time varchar(30)
declare @t TIME
declare @hour varchar(2)
declare @minute varchar(2)
set @hour='16'
set @minute='03'
set @time=(@hour+':'+@minute+':00:000') 
set @t = CONVERT(TIME, @time, 114)	-- 114 thr formate hh:mi:ss:mmm(24h)
--print @t

 
print @date +' ' +@time			--string in total
print CAST(@d AS DATETIME)+ @t	--datetime in total

to know about Datetime formates
http://msdn.microsoft.com/en-us/library/ms187928.aspx[^]
http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx[^]
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

hi srishti_

here is solution

declare @date varchar(10)
declare @d datetime
declare @hour varchar(2)
declare @minute varchar(2)
declare @final varchar(50)
set @date='10/14/2014'
set @hour='16'
set @minute='03'
set @final=(@date+' '+@hour+':'+@minute+':00:000')
set @d =CONVERT(DATETIME, @final)
print @final
print @d


i changed from declare @final varchar(20)to declare @final varchar(50)

pls check it is working fine now
  Permalink  
Comments
Vinay Mistry 21-Aug-14 9:13am
   
Please provide comments that after changing declare @final varchar(20)to declare @final varchar(50) your query has been solved or not?
srishti_ 21-Aug-14 9:14am
   
its working fine with varchar(16) but it showinw output like
'Oct 14 2014 4:03PM' and i want output like '10/14/2014 16:03:00:000' how can it be
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

changed the @final variable size to 50
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160721.1 | Last Updated 21 Aug 2014
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100