Solved Problem with changing data type

[Kayleigh]

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?

 

[pbaldy]

I would check the values in the table. There may be a value outside the range that datetime can accept.

 

[Kayleigh]

The table has 8000+ records. How would I be able to single that out?

 

[jdraw]

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]

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]

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.

 

[CJ_London]

datetime2 has a higher level of precision than datetime and a greater date range - see this link https://database.guide/datetime-vs-datetime2-in-sql-server-whats-the-difference/

Not sure if the time element would be truncated when converting to datetime or create an error but perhaps something else to look at if it does create an error.

 

[Isaac]

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]

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]

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.

 

[Pat Hartman]

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.