0

First post/question here. I'm trying to convert an nchar to datetime. The examples I looked for all displayed converting to 12 hour from 24 or just dealing with time and not the full date with conversion. I'm not a native to SQL and I'm still learning the basics, so any help is appreciated.

I have a table with this format that I've read from a bulk insert of .txt files.

Alarm is nchar of text,

AlarmTime is also nchar of the date I read from the text file.

Alarm    |    AlarmTime
  1      |      7/6/2016 10:10:42 AM
  2      |      7/10/2016 6:41:23 PM
  3      |      8/4/2016 4:35:53 PM

I would like to get this into a datetime format and not be store as nchar. Whenever I try to convert I get an error: Conversion failed when converting date and/or time from character string.

The result I'm looking for is this just stored as datetime:

    Alarm    |    AlarmTime
      1      |      7/6/2016 10:10:42 
      2      |      7/10/2016 18:41:23 
      3      |      8/4/2016 16:35:53

I have considered separating the date and time in to separate columns using a delimiter and then putting them both back together later. Or using the PM to add time then convert the colum to datetime. But those both seemed cumbersome, and I figured there was something easy I was missing to accomplish this.

Thanks in advance!

Spex1848
  • 3
  • 1

1 Answers1

0

Since you are on 2012, I would suggest that you use Try_Convert(). This will trap any conversion errors. (Assuming your data string is m/d/y)

To identify the bogus records, try:

Select * from YourTable where Try_Convert(datetime,AlarmTime) is null

Sample Code to Reformat your data

Declare @YourTable table (Alarm int,AlarmTime nvarchar(50))
Insert Into @YourTable values
(  1,'7/6/2016 10:10:42 AM'),
(  2,'7/10/2016 6:41:23 PM'),
(  3,'8/4/2016 4:35:53 PM')

Select A.*
      ,ReFormated = format(try_convert(datetime,AlarmTime),'M/d/yyyy HH:mm:ss')
      ,AsDateTime = try_convert(datetime,AlarmTime)
 From  @YourTable A

Returns

Alarm   AlarmTime               ReFormated          AsDateTime
1       7/6/2016 10:10:42 AM    7/6/2016 10:10:42   2016-07-06 10:10:42.000
2       7/10/2016 6:41:23 PM    7/10/2016 18:41:23  2016-07-10 18:41:23.000
3       8/4/2016 4:35:53 PM     8/4/2016 16:35:53   2016-08-04 16:35:53.000
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • I haven't heard of try_convert. But I ran and completed without errors, however the colum remains null. Following and running your example code everything runs correctly. So I think my issue may be with how I'm reading in and storing my date in the colum. I'll try removing any leftover spaces and see if that helps. Thanks! – Spex1848 Apr 21 '17 at 16:18
  • @Spex1848 Happy it helped. I suspected some bogus data. Always risky storing datetimes as a string... too many things can go wrong. – John Cappelletti Apr 21 '17 at 16:20
  • @Spex1848 Just curious when you ran Select * from YourTable where Try_Convert(datetime,AlarmTime) is null how many records where returned? All or just a handful? – John Cappelletti Apr 21 '17 at 16:22
  • I just tried again using LTRIM. When I run the select you described above it returns all of the records. So that means at no point did i get a proper conversion. I'm pretty sure now that the issue is with the string I'm passing. Trying now to see how I can get that in order then test out converting again. Thanks again! – Spex1848 Apr 21 '17 at 17:25
  • @Spex1848 see my answer here http://stackoverflow.com/questions/43148767/sql-server-remove-all-non-printable-ascii-characters/43149139#43149139 Perhaps you have control characters in your string – John Cappelletti Apr 21 '17 at 17:29