SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Convert Char to date in Database level(Sql severe2
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chalamasql
Starting Member

India
4 Posts

Posted - 06/05/2013 :  03:27:54  Show Profile  Reply with Quote
Hi,

we have a date column "Last Edited On" in the database. the values in the column is loaded in the format of "dd/mm/yy hh:mm:ss" as
character data type.But i need dd/mm/yyy in Datetime format please help me

I tryed like this

Select dbo.STAR_INP_GIT_STATUS_REP."Last Edited on" ,
Convert(DateTime,dbo.STAR_INP_GIT_STATUS_REP."Last Edited on",103)
---CAST(CONVERT(char(50),dbo.STAR_INP_GIT_STATUS_REP."Last Edited on",103)AS DateTime)
from dbo.STAR_INP_GIT_STATUS_REP

But its not working ppleae help me any wrong in above code





chalama reddy

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/05/2013 :  03:35:09  Show Profile  Reply with Quote
if format is consistent you can use


Select dbo.STAR_INP_GIT_STATUS_REP."Last Edited on" ,
LEFT(dbo.STAR_INP_GIT_STATUS_REP."Last Edited on",CHARINDEX(' ',dbo.STAR_INP_GIT_STATUS_REP."Last Edited on")-1) AS DateVal
---CAST(CONVERT(char(50),dbo.STAR_INP_GIT_STATUS_REP."Last Edited on",103)AS DateTime)
from dbo.STAR_INP_GIT_STATUS_REP


please try to use appropriate datatype for field. it shoulde be datetime if it is to store dates.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/05/2013 :  03:39:54  Show Profile  Reply with Quote
-- Different approaches
DECLARE @date CHAR(50) = '05/06/13 13:15:30'
SELECT CAST(@date AS DATE) -- 2013-05-06
SELECT CAST( @date AS CHAR(8)) -- 05/06/13
SELECT CONVERT(VARCHAR(8), @date, 103) -- 05/06/13

EDIT: forgot to mention that DATE data type is available from MSSQL 2008 onwards...
--
Chandu

Edited by - bandi on 06/05/2013 04:48:14
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/05/2013 :  03:43:01  Show Profile  Reply with Quote
quote:
Originally posted by bandi

-- Different approaches
DECLARE @date CHAR(50) = '05/06/13 13:15:30'
SELECT CAST(@date AS DATE) -- 2013-05-06
SELECT CAST( @date AS CHAR(8)) -- 05/06/13
SELECT CONVERT(VARCHAR(8), @date, 103) -- 05/06/13

--
Chandu


There's no point in converting to date in this case as OP still wants the final format in same way just stripping time part
thats why i suggested a string based approach to avoid double conversion.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000