I have two columns in my table: open, close of the nvarchar data type. They have values like this ’10:50:PM’. they’re supposed to be of the time data type.
I cannot perform any time-related queries on the columns. I’ve tried casting, converting, updating and altering the columns but it didn’t work. It keeps throwing the error message: conversion failed when converting date and/or time from character string
6
SQL expects time strings that are being imported to be in a specific format to allow for casting or converting into a TIME data type. The values in your columns (’10:50:PM’) are not in a valid time format because the correct format for AM/PM time should be ‘hh:mm:ss AM/PM’.
You either need to correct your format in the csv file you’re importing from, or re-format your columns in SQL after importing.
You could use the STUFF function to insert a space before the AM/PM, then the time string becomes ’10:50:00 PM’.
UPDATE tableName
SET Open = STUFF(Open, LEN(Open) - 1, 0, ' '),
Close = STUFF(Close, LEN(Close) - 1, 0, ' ');
Ave17 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
I had to manually change the data in Excel then imported the flat file to SQL. It showed the data type as TIME, so everything works alright..