Help with convert to date from string

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

General SQL Server Forums

New to SQL Server Programming

Help with convert to date from string

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-10-29 : 13:10:38

In our ERP system we have a field which is a date-picker in the user front end, but the value is stored in an NVARCHAR field and not always consistently. How can I convert this to a date (preferably in the format YYYY-MM-DD HH:MM) that I could use in a calculation?

select code, spec_value from spec_checklist_remind where spec_checklist_id = 17

code spec_value
------------ -----------------------------------------------------------------------
05MC0001 22/07/2014
05MC0002 23/07/2014
06MT0001 01-May-2014
06MT0002 01-May-2014
06MT0006 01/05/2014
06MT0007 01-May-2014
06MT0008 01/05/2014

Many thanks
Martyn

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-29 : 14:28:57

If you are on SQL Server 2012 or better, you can use TRY_CONVERT(): http://msdn.microsoft.com/en-us/library/hh230993.aspx

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-10-29 : 15:55:56

Sadly not, we are still on 2008, so I can't try that unfortunately.

Any other suggestions would be gratefully received though.

Thanks
Martyn

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-10-29 : 18:01:10

From the sample data you provided, I see 2 date formats. This ought to cover it:

select code
      ,convert(date,replace(spec_value,'-',' '),case when charindex('/',spec_value)>0 then 103 else 106 end as yourdate
  from spec_checklist_remind
 where spec_checklist_id=17

This will return the date, as a db date, which is very suitable for calculation.

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-10-30 : 06:39:26

This looks like what I'm trying to achieve, but I get the following error when I try to run the query:

[code]Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.[\code]

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-10-30 : 14:34:12

quote:
Originally posted by wembleybear

This looks like what I'm trying to achieve, but I get the following error when I try to run the query:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.[\code]

My bad - missing end bracket on convert. Try this instead:[code]select code
,convert(date,replace(spec_value,'-',' '),case when charindex('/',spec_value)>0 then 103 else 106 end) as yourdate
from spec_checklist_remind
where spec_checklist_id=17

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources