RIAS
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
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
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.
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)
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!
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!
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.
?
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.
?
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!
Please refer my last comment. Why do you want to convert the data? You will not get any benefit from it.
Hope it helps!
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.
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.
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.
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 .
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!
Hope it helps!
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.
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.
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]Can you provide the regional settings from the server as well the collation used by the SQL Server instance?
ALTER COLUMN [DepartureDate] Date NULL
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
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.
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?
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
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
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.
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!!!!
ASKER
Yes Vitor,
Please can you giude on how can I achieve my goal.
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?
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?
In 12/1/2017, what is the month and what is the date? Is it Jan 12 or Dec 01?
ASKER
No i rolled back the entire table.
The field is now nvarchar with lots of text in it.
The field is now nvarchar with lots of text in it.
ASKER
Nakul,
It is Jan 12
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')
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.
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
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.
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'
ASKER
default language 0 9999 0 0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
ASKER
Yes,Vitor that worked! What next
If it's all done, including the backup you can go now for the ALTER COLUMN step.
ASKER
oki
ASKER
Perfect! Worked like charm!
Pawan Thanks for the efforts!
Pawan Thanks for the efforts!
Perfect! Worked like charm!Did you perform all the tests to assure that dates are now stored as desired?
ASKER
Yes!
ASKER
I have done all these on my backup.Now will do it on live
Good. Testing is the more important phase.
Cheers
Cheers
The source target format is always the same?