update datetime value to string column and then output table to excel via SSIS

nononame2021 256 Reputation points
2022-06-22T08:51:06.347+00:00

my table column datatype is datetime 2022-06-06 00:00:00.000
i would like to update above value to another table's column which is decleared as nvarchar(100), however, once i update the above value to nvarchar(100) column, it will become Jun 6 2022 12:00AM
how can i update datetime value to nvarchar column as 2022-06-06 00:00:00.000 or can it be remove the time so just stored 2022-06-06 ?

how to update datetime value to nvarchar column as this format 2022-06-06 (yyyy-mm-dd)? (i need to update "info" column where id=2)

213747-image.png

finally, my purposed of updating this table is to output to excel. but now id=2 it is in wrong date format. how to update as dd-mm-yyyy in database table.

213762-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,482 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,437 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Zelazny 1 Reputation point
    2022-07-25T08:58:03.943+00:00

    You should look at the cast-and-convert-transact-sql page

    For example, yyyy-mm-dd is 23 on the list there, so you can use:

    convert(nvarchar, DateCol, 23)   
    

    to return a date formatted as yyyy-mm-dd. If you want hours, minutes and seconds as well, you can look at 20, 120, 21, 25 or 121.

    The issue here is that your column doesn't actually seem to be a date column - but a text column. To fix this, you will need to convert into a date, then back out - something like this:

    drop table if exists #dummydata  
      
    create table #dummydata (id int, title nvarchar(20), info nvarchar(50))  
      
    insert into #dummydata  
    select 1, 'abc', 'ii'  
    UNION select 2,'eee','6 Jun 2022 12:00AM'  
    UNION select 3, 'www', 'billy'  
      
    select * from #dummydata  
      
    update #dummydata set info = CONVERT(nvarchar, convert(datetime, info), 23) where id=2  
      
    select * from #dummydata  
    
    0 comments No comments