Link to home
Start Free TrialLog in
Avatar of RIAS
RIASFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Changing the datatype of a column from nvarchar to date

Hello,
I tried changing the datatype of a column from nvarchar to date.
It changed the format from dd/mm/yyyy to mm/dd/yyyy.
Example data which was like 06/01/2017 got changed to 01-06-2017.


Cheers
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

What is the desired target format?
The source target format is always the same?
It's also good to know how are you trying now.
For example: SELECT CAST('06/01/2017' AS DATE)
will return in the format that your computer has defined in the Regional Settings.
mm/dd/yyyy is mostly a North American format (in Europe we use dd-mm-yyyy) so you can try to use CONVERT with the U.S. format (101):
SELECT CONVERT(DATE, '06/01/2017', 101)

Open in new window

Hello Rias,

Do not worry about how the dates are stored. Its just that when you fetch you should define the format.
There is no point in providing the format of the date while inserting data.

Dates are internally stored as INT. It is based on the computer settings.

Hope it helps!
Avatar of RIAS

ASKER

So to summarise Vitor,

First  I will  move the data to another field.
Then change the isdate(field1) to SELECT CONVERT(DATE, '06/01/2017', 101)
and then change the dateformat (nvarchar) field to date.

?
Avatar of RIAS

ASKER

Hwo to convert the entire column data to SELECT CONVERT(DATE, '06/01/2017', 101)?
Hi Rias,
Please refer my last comment. Why do you want to convert the data? You will not get any benefit from it.

Hope it helps!
Avatar of RIAS

ASKER

Pawan,

The datatype conversion I did yesterday ,didnt quite go correctly. The dates got all messed up.
 data which was like 06/01/2017 got changed to 01-06-2017.
Avatar of RIAS

ASKER

So i had to roll back my table and trying to change the datatype of a field from nvarchar to date again.
So what is the problem there? That is also a valid date. You just need to format when you are fetching.
The dates got all messed up.  data which was like 06/01/2017 got changed to 01-06-2017.
Can you show how you did the conversion. If it has converted as dd-mm-yyyy instead of mm/dd/yyyy for sure it would mess everything (the engine would store the expect day as month and month as day). The only dates that might been well converted is those one where the day were higher than 12, so the engine will read them as month and not as day.
Avatar of RIAS

ASKER

Nope Pawan, The reports went wrong, i can't change the data extract query of the report as its already live.
The data displayed in the live application was shown wrong .
You just have to use the FORMAT function. Can't you change the query of the report. It is the best place to change. or you can also change the format of the column in SSRS report.

Hope it helps!
Avatar of RIAS

ASKER

Yes,but what about the live vb.net application. Can change that.
Why not fix the database table instead of fixing million app which use it.
The date is still correct  (it was January 06, 2017 before, it is January 06, 2017 now).

Please note that a database system only stores the data. The date which you see when you query via an application or in SSMS is a rendered format.

Rendering should be done at the level that consumes the date, i.e. by the application or by the report. You would ideally want to render the date in the format of the current regional settings in effect where the application or report are running. Hence, I would not even bother about rendering the date in a query. Pass the date in the standard ODBC format (yyyy-mm-dd hh:mi:ss) to the application and let the application format it as required.
So, this is the part of code that you wrote for the conversion:
ALTER TABLE [dbo].[CORRESPONDENCE]
    ALTER COLUMN [DepartureDate] Date NULL
Can you provide the regional settings from the server as well the collation used by the SQL Server instance?
Avatar of RIAS

ASKER

Simple question:

I have a nvarchar field :

Field1
12/1/2017  

now I need to change the nvarchar field to date datatypeformat.
Please let me know the steps
Are you willing to change the regional settings ? I hope not.
Avatar of RIAS

ASKER

Can you provide the regional settings from the server as well the collation used by the SQL Server instance?

How can i get ti?
The date is still correct  (it was January 06, 2017 before, it is January 06, 2017 now).
Not really if it was stored as '06/01/2017' as the application was expecting to read the first part as month (it should be June) and now it will find '01' (January).
You can change that using CONTROL -> REGION -> DATE AND TIME FORMATS -> ADDITIONAL SETTINGS -> OK
Avatar of RIAS

ASKER

Pawan,
Please don't ask me to change any settings.
Please guide me to
I have a nvarchar field :

Field1
12/1/2017  

now I need to change the nvarchar field to date datatypeformat.
Please let me know the steps
Hi Rias,
Without that I am afraid there is no option. You can keep the dates as it is in NVARCHAR column.

In that just make sure that you will not enter any junk data or invalid dates.
No, do not change the Regional Settings from a server. Is quite danger and will affect everything!!!!
Avatar of RIAS

ASKER

Yes Vitor,

Please can you giude on how can I achieve my goal.
Yes, this can be done.
Please let me know in which stage are you know. I understood that you rolled back some changes but which ones?
Hello!

In 12/1/2017, what is the month and what is the date? Is it Jan 12 or Dec 01?
Avatar of RIAS

ASKER

No i rolled back the entire table.
The field is now nvarchar with lots of text in it.
Avatar of RIAS

ASKER

Nakul,
It is Jan 12
Hi Rias

try like this...

1. Add a new column with DATE datatype
2. Insert all data into it from that nvarchar column
3. UPDATE Table_Name
SET NewDATE = FORMAT(NewDATE , 'MM/dd/yyyy')
The field is now nvarchar with lots of text in it.
Ok. Do everything you did yesterday but the part of the ALTER TABLE. When you reach that stage perform a backup and let me know.
Avatar of RIAS

ASKER

Ok. Thanks

Please find the sample data:
DepartureDate  is that nvarchar field

Sample Data:
ArrivalDate     DepartureDate

03/03/2016      11/03/2016      
21/02/2016      04/03/2016      
24/02/2016      02/03/2016      
28/02/2016      22/03/2016      
29/02/2016      14/03/2016      
02/03/2016      03/03/2016      
01/03/2016      15/03/2016      
20/02/2016      28/02/2016      
20/02/2016      24/02/2016      
01/03/2016      04/03/2016      
30/03/2016      31/03/2016      
01/03/2016      31/03/2016      
05/03/2016      19/03/2016      
04/03/2016      11/03/2016      
07/03/2016      21/03/2016      
06/03/2016      11/03/2016      
05/03/2016      07/03/2016      
05/03/2016      09/03/2016      
1/03/2016      10/04/2016
04/03/2016      05/03/2016      
13/03/2016      21/03/2016      
04/04/2016      18/04/2016      
17/03/2016      25/03/2016      
28/03/2016      09/04/2016      
14/03/2016      01/04/2016
07/04/2010      2 Nights
08/04/2010      4 Nights
06/04/2010      1 Night
10/03/2010      7 Ni
06/04/2010      7 Nights
05/05/2009      4 Nights
08/04/2010      1 night
09/04/2010      3 Nights
13/04/2010      1 night
13/04/2010      2 nights
13/04/2010      3 Nights
13/04/2010      4 Nights
18/12/2015      20/12/2015
10/01/2016      23/02/2016
15/01/2016      18/01/2016
15/01/2016      21/01/2016
17/01/2016      19/01/2016
11/01/2016      15/01/2016
08/01/2016      11/01/2016
13/01/2016      27/01/2016
10/01/2016      16/01/2016
10/02/2011      2 weeks subj to extend
16/02/2001      10 Nights
13/02/2011      8 Nights subj. to extend
14/02/2011      1 Week Subj. to extend
12/04/2011      2 Nights
13/04/2011      1 Night
12/04/2011      1 week subj to extend
13/04/2011      14 Nigh
11/04/2011      1 Night
12/04/2011      7 Nights
14/04/2011      5 Nights
14/04/2011      7 Nights
Hi Rias,
Please try my last suggestion. I think that should help.
RIAS, other thing that I need to know is the result of the following query:
exec sp_configure 'default language'

Open in new window

Avatar of RIAS

ASKER

default language      0      9999      0      0
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RIAS

ASKER

Vitor,
Before i do :
SET DATEFORMAT dmy;

ALTER TABLE [dbo].[CORRESPONDENCE]
     ALTER COLUMN [DepartureDate] Datetime NULL

SET DATEFORMAT mdy;

Can i do :



UPDATE CORRESPONDENCE1
SET Comments = ISNULL(Comments,'') + '; Departure Date: ' + DepartureDate,
   DepartureDate = NULL
 WHERE TRY_CONVERT(DATE, DepartureDate) IS NULL
Yes. You're not converting nothing in that statement.
Avatar of RIAS

ASKER

Yes,Vitor that worked! What next
If it's all done, including the backup you can go now for the ALTER COLUMN step.
Avatar of RIAS

ASKER

oki
Avatar of RIAS

ASKER

Perfect! Worked like charm!
Pawan Thanks for the efforts!
Perfect! Worked like charm!
Did you perform all the tests to assure that dates are now stored as desired?
Avatar of RIAS

ASKER

Yes!
Avatar of RIAS

ASKER

I have done all these on my backup.Now will do it on live
Good. Testing is the more important phase.
Cheers