Solved Problem with changing data type (1 Viewer)

Kayleigh

Member
Local time
Today, 20:45
Joined
Sep 24, 2020
Messages
706
Hi,
I am having another issue with the SQL server. Using SSMS I am trying to change datetime2 (default from Access migration) data type to datetime (works better for many calculations/queries). It works for some fields but for others I get the following error message. Is there any way around this?
 

Attachments

  • Screenshot 2021-12-13 210138.png
    Screenshot 2021-12-13 210138.png
    23.2 KB · Views: 321

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:45
Joined
Aug 30, 2003
Messages
36,118
I would check the values in the table. There may be a value outside the range that datetime can accept.
 

Kayleigh

Member
Local time
Today, 20:45
Joined
Sep 24, 2020
Messages
706
The table has 8000+ records. How would I be able to single that out?
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:45
Joined
Jan 23, 2006
Messages
15,364
You may get some insight here. I'm not a sqlserver person , but you may want to check for a missing or incorrect value(null, incorrect date...)
 

Minty

AWF VIP
Local time
Today, 20:45
Joined
Jul 26, 2013
Messages
10,355
Query the table to output the top 10 results and sort by the date value DESC, then do it again ASC.

That will show you the extremes of your values...
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:45
Joined
Aug 30, 2003
Messages
36,118
I did what Minty described when I upsized a client's db from Access to SQL Server. Found dates like 12/14/202 which Access didn't care about but SQL Server did.
 

Isaac

Lifelong Learner
Local time
Today, 13:45
Joined
Mar 14, 2017
Messages
8,738
this is crude, but maybe cast them to a varchar (of great length, say, 50), then see what the longest ones are - I say this is 'crude', because it might be effective but admittedly is a tool in lieu of actually knowing how to code for the extreme values - which I don't know how to do either, as I've never used any datetime datatype in sql other than datetime. Can't really see the point of the rest of them.

Either way, why not just create a SECOND column - of datetime - and

update tablename set datetimecolumn = cast(datetime2column as datetime)

then remove the datetime2 column
then rename the datetime column to the original name you wanted/what the datetime2 column had
 

Kayleigh

Member
Local time
Today, 20:45
Joined
Sep 24, 2020
Messages
706
I tried @Minty 's suggestion. Eventually found couple of out of range values.

@Isaac your second suggestion worked like a dream - have no further issues with that column now!

It's been a nightmare converting all the date/time columns. This is the best option by far.
 

Isaac

Lifelong Learner
Local time
Today, 13:45
Joined
Mar 14, 2017
Messages
8,738
I tried @Minty 's suggestion. Eventually found couple of out of range values.

@Isaac your second suggestion worked like a dream - have no further issues with that column now!

It's been a nightmare converting all the date/time columns. This is the best option by far.
Great!

I was just thinking of modifying my suggestion to use Try_Cast, then later querying the Nulls to handle any problem exceptions, but if that statement successfully ran to convert them all, you might be good to go.

My skill level with dates/times in SQL Server is fairly basic, (as I've never had a business or technical need to do otherwise in eons), so...I'd say spot check your results for being accurately converted in case I failed to mention a possible "gotcha".

Anyway, glad it worked. (y)
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:45
Joined
Feb 19, 2002
Messages
42,976
I always use the asc, desc sorting method and fix the outliers. And since I know this is a common error, I do it before I attempt to do the conversion UNLESS, I created the app and properly validated dates as they were entered.
Unless you are entering historical data, a year of 202 is simply not valid and is easily excluded at the time of data entry. You can't always ensue accurate dates but you can ensure rational ones.
 

Users who are viewing this thread

Top Bottom