August 19, 2015 at 6:13 am
Hi, I am trying to convert a column (nvarchar) to date time.
Here is an example of some values:
20110905
20110901
20111003
As expected, I received the following error: 'Conversion failed when converting date and/or time from character string.'
However, I am unsure how I should approach updating the whole column. I would like the format to be '103'.
Cheers,
Chris
August 19, 2015 at 6:31 am
As far as I know, I think you won't have a choice to go this route...
create table test (mydates varchar(10))
Insert into test values (20110905),(20110901),(20111003)
select mydates,
convert(varchar(10),convert(datetime,substring(mydates,1,4) + '-' + substring(mydates,5,2) + '-' + substring(mydates,7,2)),103)
from test
I don't know of another way.
Thanks
JG
August 19, 2015 at 6:35 am
The string format YYYYMMDD can be converted directly to datetime.
declare @x nvarchar(20) = '20110905';
declare @y datetime;
set @y = @x;
select @y;
Display formats are, of course, different from storage formats. It is unclear from your post exactly what you want to do with the datetime results.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 19, 2015 at 6:43 am
Phil Parkin (8/19/2015)
The string format YYYYMMDD can be converted directly to datetime.
declare @x nvarchar(20) = '20110905';
declare @y datetime;
set @y = @x;
select @y;
Display formats are, of course, different from storage formats. It is unclear from your post exactly what you want to do with the datetime results.
I hope you want to replace the string column with a datetime column and that's the purpose of your conversion. You should always use date data types to store dates. It allows date math and provides inherent validation by the data type. You won't, for example, be allowed to get the value 20150899 in a datetime, but you could in an integer. A string would even allow the value 2015bad1, which will, as far as I know, never convert to any sort of datetime.
August 19, 2015 at 7:11 am
Hi,
Just an additional tip: Ensure that text column values do not have any kind of escape character at the beginning or the end during conversion. For example while importing a windows binary performance log file (.blg) to SQL Server using relog.exe tool, it adds an extra char at the end of a nvarchar column containing the date and time of the capture, invisible on SSMS output. Use LEN, LTRIM or RTRIM functions just in case.
Regards
August 19, 2015 at 7:22 am
aldous.chris (8/19/2015)
As expected, I received the following error: 'Conversion failed when converting date and/or time from character string.'
As mentioned, the format YYYYMMDD will automatically convert to datetime without the need of format and regardless of regional settings (thanks to the ISO 8601).
Now, If you're receiving an error, it must be that you have values that aren't allowed.
Try something like these options:
SELECT TheNVarcharColumn
FROM TheTable
WHERE ISDATE( TheNVarcharColumn) = 0
--AND/OR
SELECT TheNVarcharColumn
FROM TheTable
WHERE TheNVarcharColumn NOT LIKE '[1-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9]' --This can be more complicated for better validations.
August 19, 2015 at 9:19 am
Hi All,
Thanks for your help and quick responses. There were values which weren't allowed in the column!
All columns have been successfully updated!
Cheers,
Chris
August 19, 2015 at 9:10 pm
aldous.chris (8/19/2015)
Hi All,Thanks for your help and quick responses. There were values which weren't allowed in the column!
All columns have been successfully updated!
Cheers,
Chris
There are very few places in SQL Server that have nearly absolute rules but one of them is that you should never store dates or times in any kind of character based or numeric type of column. You should be using one of the date/time datatypes instead.
Heh... now let me guess. Is the current column NVARCHAR(256)?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2015 at 5:38 am
Jeff Moden (8/19/2015)
aldous.chris (8/19/2015)
Hi All,Thanks for your help and quick responses. There were values which weren't allowed in the column!
All columns have been successfully updated!
Cheers,
Chris
There are very few places in SQL Server that have nearly absolute rules but one of them is that you should never store dates or times in any kind of character based or numeric type of column. You should be using one of the date/time datatypes instead.
Heh... now let me guess. Is the current column NVARCHAR(256)?
That would be the icing on the cake, wouldn't it? 😛
If you can do so, take the opportunity you have now while your data is clean to get the column changed to a date date type.
August 24, 2015 at 7:43 am
you can use SQL string functions (Left, Right, Substring) to split out "date" parts.
Then can concatenate and apply a "cast ... as datetime" clause.
Always worked for me.
August 24, 2015 at 7:52 am
Hopefully you are converting all your "CharDate" columns to Date and never looking back. However, if there will still be some tables or databases containing formatted date values in Char columns, you can use a check constraint to insure the application always inserts values in valid YYYYMMDD format.
For example, the following insures two things: that the value can be converted to Date and that the value is in YYYYMMDD format.
create table foo
(
foo_date varchar(30) not null
constraint ck_foo_date_yyyymmdd
check (foo_date = convert(char(8),cast(foo_date as date),112))
);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 24, 2015 at 8:21 am
DuncEduardo (8/24/2015)
you can use SQL string functions (Left, Right, Substring) to split out "date" parts.Then can concatenate and apply a "cast ... as datetime" clause.
Always worked for me.
That might not be needed most of the time.
I've seen it done and people split a perfectly valid YYYYMMDD format which isn't language or settings dependent, to a YYYY-MM-DD which can be affected by regional settings.
If you can show me an example on what format can't be handled by a format code using a CONVERT, I'll get your point. Otherwise, I'll strongly suggest that you stop doing additional work.
That said, the issue in here, where invalid values that caused the cast to fail.
August 27, 2015 at 11:39 am
Jeff Moden (8/19/2015)
There are very few places in SQL Server that have nearly absolute rules but one of them is that you should never store dates or times in any kind of character based or numeric type of column. You should be using one of the date/time datatypes instead.
Heh... now let me guess. Is the current column NVARCHAR(256)?
Off topic war stories...
I worked on an app for a while that stored dates as sql_varient. A start and end date would be entered on a web form, and then then elapsed time would be calculated by parsing the dates out of the varient field. The developers loved to claim that the database was slow.
August 27, 2015 at 11:58 am
tim_harkin (8/27/2015)
Jeff Moden (8/19/2015)
There are very few places in SQL Server that have nearly absolute rules but one of them is that you should never store dates or times in any kind of character based or numeric type of column. You should be using one of the date/time datatypes instead.
Heh... now let me guess. Is the current column NVARCHAR(256)?
Off topic war stories...
I worked on an app for a while that stored dates as sql_varient. A start and end date would be entered on a web form, and then then elapsed time would be calculated by parsing the dates out of the varient field. The developers loved to claim that the database was slow.
One could argue that typing a date/time attribute as anything other than a standardized Date or Time type is a form of de-normalization. What it's doing is coding a non-conformed textual description of a date/time rather than it's true binary key value. It would be like coding "John K Smith" and/or "SMITH, JOHN K" in a PurchaseOrder table rather than CustomerID.
If we find ourselves in a situation where we must support a legacy database that contains dates in text format, then two options for at least conforming the coding of dates would be a check constraint (like the one I described above in an earlier post) or perhaps a foreign key constraint referencing a calendar table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply