Converting a column of type nvarchar to datetime

  • 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

  • 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

  • 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

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

  • 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

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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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

  • 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

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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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.

  • 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