Convert Char to date in Database level(Sql severe2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Convert Char to date in Database level(Sql severe2

Author

Topic

chalamasql
Starting Member

4 Posts

Posted - 2013-06-05 : 03:27:54

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

52326 Posts

Posted - 2013-06-05 : 03:35:09

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 03:39:54

-- 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 03:43:01

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources