May 2, 2017 at 3:50 am
I'm using SQL Server Management Studio. There is a table called Agreement in my database containing dates which are stored as nvarchar(255). I want to migrate the data of this table to a new table which I call Converted_Agreement and store this data as date. Also, I want them all to be formatted like this YYYY-MM-DD
Currently the Dates table looks like this:
[dbo].[Agreement]**Dates**
15/6/2011
16/6/2011
2013-03-30
2013-04-16
...
I want the new table to look like this:
[dbo].[Converted_Agreement]**Dates**
2011-06-15
2011-06-16
2013-03-30
2013-04-16
...
I execute this query but formatting of dates remains the same, only the data type changes from nvarchar(255) to date.
USE [reporting_database]
GO
INSERT INTO [dbo].[Converted_Agreement]
SELECT
cast(Dates as date)
FROM [dbo].[Agreement]
GO
This query changes data type from nvarchar(255) to date and migrates data from the old table to a new one, however, I don't know how to change formatting so all the dates are stored as YYYY-MM-DD Any advice?
May 2, 2017 at 3:58 am
You should use CONVERT
USE [reporting_database]
GO
INSERT INTO [dbo].[Converted_Agreement]
SELECT
CONVERT (char(10),Dates,126)
FROM [dbo].[Agreement]
GO
Worth noting is that this only affects the presentation of the data returned by your query - inserting this into a date column will not preserve any formatting decisions made in the insert statement.
SQL Server doesn't actually store a date in a given format, rather it is stored as a binary format - no amount of formatting will affect this.
May 2, 2017 at 4:02 am
What is the data type of the Dates column in your new table? If it's datetime (or similar), then dates are stored as dates, not strings. That's how it should be. What you see when you run a query is dependent on your local settings. If you want to display dates in a different format, do that in your presentation layer (Excel, SSRS etc), or use the CONVERT function to render the date in the required format.
John
May 2, 2017 at 4:02 am
Have you actually tested this? This would work, regardless of if your Dates field in the table Converted_agreement is a date or varchar(255) data type (don't use varchar(255) in your new table 😉 ).
For example, taking your small dataset:CREATE TABLE #Agreement (Dates varchar(255))
GO
INSERT INTO #Agreement
VALUES
('15/6/2011'),
('16/6/2011'),
('2013-03-30'),
('2013-04-16');
GO
CREATE TABLE #Converted_Agreement (Dates date, DatesOld varchar(255));
GO
INSERT INTO #Converted_Agreement
SELECT Dates, CAST(Dates as date)
FROM #Agreement;
GO
SELECT *
FROM #Converted_Agreement;
GO
--Clean up
DROP TABLE #Converted_Agreement;
DROP TABLE #Agreement;
GO
This returns the values:Dates DatesOld
---------- ----------
2011-06-15 2011-06-15
2011-06-16 2011-06-16
2013-03-30 2013-03-30
2013-04-16 2013-04-16
So even though DatesOld is a varchar type, it still displays in yyyy-MM-dd (as this is the display format for date data types) as it was initially cast as as date. As i said though, store your dates as a date, not a varchar. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 2, 2017 at 4:14 am
m.richardson.home - Tuesday, May 2, 2017 3:58 AMYou should use CONVERT
USE [reporting_database]
GO
INSERT INTO [dbo].[Converted_Agreement]
SELECT
CONVERT (char(10),Dates,126)
FROM [dbo].[Agreement]
GOWorth noting is that this only affects the presentation of the data returned by your query - inserting this into a date column will not preserve any formatting decisions made in the insert statement.
SQL Server doesn't actually store a date in a given format, rather it is stored as a binary format - no amount of formatting will affect this.
Thanks for the answer. It did give me an error:
Conversion failed when converting date and/or time from character string.
May 2, 2017 at 4:16 am
Thom A - Tuesday, May 2, 2017 4:02 AMHave you actually tested this? This would work, regardless of if your Dates field in the table Converted_agreement is a date or varchar(255) data type (don't use varchar(255) in your new table 😉 ).For example, taking your small dataset:
CREATE TABLE #Agreement (Dates varchar(255))
GOINSERT INTO #Agreement
VALUES
('15/6/2011'),
('16/6/2011'),
('2013-03-30'),
('2013-04-16');
GOCREATE TABLE #Converted_Agreement (Dates date, DatesOld varchar(255));
GOINSERT INTO #Converted_Agreement
SELECT Dates, CAST(Dates as date)
FROM #Agreement;
GOSELECT *
FROM #Converted_Agreement;
GO--Clean up
DROP TABLE #Converted_Agreement;
DROP TABLE #Agreement;
GOThis returns the values:
Dates DatesOld
---------- ----------
2011-06-15 2011-06-15
2011-06-16 2011-06-16
2013-03-30 2013-03-30
2013-04-16 2013-04-16
So even though DatesOld is a varchar type, it still displays in yyyy-MM-dd (as this is the display format for date data types) as it was initially cast as as date. As i said though, store your dates as a date, not a varchar. 🙂
Will try again, thanks 🙂
May 4, 2017 at 6:44 am
I tried both options and they give me the same error: conversion failed when converting date and/or time from character string.
Again, in table Agreement dates are stored as nvarchar(255), in Converted_Agreement I have to insert this data as date.
May 4, 2017 at 7:07 am
Then you must have some "dates" that can't be converted - something like "13/13/11", maybe? Try using TRY_CONVERT to find out which are the offending values.
John
May 4, 2017 at 7:10 am
John Mitchell-245523 - Thursday, May 4, 2017 7:07 AMThen you must have some "dates" that can't be converted - something like "13/13/11", maybe? Try using TRY_CONVERT to find out which are the offending values.John
These ones:
('15/6/2011'),
('16/6/2011')
I have hundreds of them like that in the table and in a new one want to have them formatted this way YYYY-MM-DD and stored as date...
By the way, I am using MS SQL Server Management Studio (2014)
May 4, 2017 at 7:14 am
I'd hazard a guess that your dates are in the format 'd/M/yyyy' (for example 4/5/2017 (4 May 2017), 14/4/2017 (14 April 2017), 5/12/2016 (5 December 2016)) , and likely your language is set to English (rather than British English for example). English would read the above dates in the format M/d/yyyy, so it would read 14/4/2017 as the 4th day of the 14th month; which is not going to work.
Are you able to confirm if my assuming on the formatting is true for your current data?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 4, 2017 at 7:18 am
Like I said before, values in a date column are stored as a number, not with any particular format. The format you see when you select from the table in SSMS are down to local settings.
Are you saying that if you run SELECT CAST(Dates AS date) FROM dbo.Agreement, you get the conversion error?
John
May 4, 2017 at 7:25 am
Thom A - Thursday, May 4, 2017 7:14 AMI'd hazard a guess that your dates are in the format 'd/M/yyyy' (for example 4/5/2017 (4 May 2017), 14/4/2017 (14 April 2017), 5/12/2016 (5 December 2016)) , and likely your language is set to English (rather than British English for example). English would expect the dates to be in the format M/d/yyyy, so it would read 14/4/2017 as the 4th date of the 14th month; which is not going to work.Are you able to confirm if my assuming on the formatting is true for your current data?
Could you help me out with this one? I don't know how to check it. I'm doing a dba's work although I am a business analyst and not experienced at all in administration of databases...
May 4, 2017 at 7:26 am
John Mitchell-245523 - Thursday, May 4, 2017 7:18 AMLike I said before, values in a date column are stored as a number, not with any particular format. The format you see when you select from the table in SSMS are down to local settings.Are you saying that if you run SELECT CAST(Dates AS date) FROM dbo.Agreement, you get the conversion error?
John
Yes, I am getting an error there.
May 4, 2017 at 7:33 am
Right then, as I said, use TRY_CONVERT to identify the offending values.
John
May 4, 2017 at 7:42 am
rutos.mer - Thursday, May 4, 2017 7:25 AMThom A - Thursday, May 4, 2017 7:14 AMI'd hazard a guess that your dates are in the format 'd/M/yyyy' (for example 4/5/2017 (4 May 2017), 14/4/2017 (14 April 2017), 5/12/2016 (5 December 2016)) , and likely your language is set to English (rather than British English for example). English would expect the dates to be in the format M/d/yyyy, so it would read 14/4/2017 as the 4th date of the 14th month; which is not going to work.Are you able to confirm if my assuming on the formatting is true for your current data?
Could you help me out with this one? I don't know how to check it. I'm doing a dba's work although I am a business analyst and not experienced at all in administration of databases...
Ok, let's ASSUME they are all in the d/M/yyyy format in your varchar column.
Does this return any rows? if so, what are the values? (Replace #Dates with your table name).
WITH FormattedDates AS (
SELECT D.Dates, CASE WHEN 0 NOT IN (S1.C, S2.C) THEN SUBSTRING(D.Dates,S2.C+1, LEN(D.Dates)) + RIGHT('0' + SUBSTRING(D.Dates,S1.C+1, S2.C - S1.C - 1),2) + RIGHT('0' + LEFT(D.Dates, S1.C-1),2) END AS FormattedDate,
S1.C AS CharIndex1, S2.C AS CharIndex2
FROM #Dates D
CROSS APPLY(VALUES(CHARINDEX('/',D.Dates))) S1(C)
CROSS APPLY(VALUES(CHARINDEX('/',D.Dates, S1.C + 1))) S2(C)
)
SELECT FD.Dates, FD.FormattedDate
FROM FormattedDates FD
WHERE TRY_CONVERT(date,FD.FormattedDate) IS NULL
OR 0 IN (CharIndex1, CharIndex2);
rutos.mer - Thursday, May 4, 2017 7:10 AMThese ones:('15/6/2011'),
('16/6/2011')I have hundreds of them like that in the table and in a new one want to have them formatted this way YYYY-MM-DD and stored as date...
By the way, I am using MS SQL Server Management Studio (2014)
Considering you've posted in the SQL 2016 forums, which version of SQL are you therefore using? If you have SQl 2016, why are you using SSMS 2014?
EDIT: Cause it really bugs me when SSC takes my SQL formatting and puts it through a Blentech blender...
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply