Sql-server – How to convert data type from varchar to number in SQL Server

sql server

I want to convert a field currently in varchar data type to number data type in the Views of SQL Server say (dbo.CUSTOMER.STORE_NBR) from Varchar to number.
How to do that ?
Will CONVERT function will do the necessity?
If yes, can you help us with syntax?

Best Answer

In Sql Server 2012 and up: each of these will return null when the conversion fails instead of an error.

So if you want to change the way a view is returning the value you would use something like:

alter view dbo.myView as 
  select
    ....
    , store_nbr = try_cast(dbo.customer.store_nbr as int)
    ....

Related Solutions

SQL Server – Fixing Varchar to Datetime Conversion Out-of-Range Error

I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format.

Try this:

select count(*) 
  from dbo.profile 
  where [created] between convert(datetime,'2014-11-01T00:00:00.000') 
                      and convert(datetime,'2014-11-30T23:59:59.997');

Querying using dates and/or datetimes can be tricky, to make sure you are getting what you are looking for I recommend reading:

Aaron Bertrand's Bad habits to kick : mis-handling date / range queries
Robyn Page's SQL Server DATE/TIME Workbench

edit: to clarify the out of range value in your error message would be from interpreting the month as 30 and the day as 11.

SQL Server – How to Convert VARCHAR to DATETIME Format

There is too much precision in the varchar to be converted into datetime.

One option(better in my opinion) would be to change the target column to datetime2(7). Then you can convert like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(@dt as datetime2(7));

If changing the column is not an option the you can do the conversion like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(cast(@dt as datetime2(7))as datetime)

Best Answer

Related Solutions

SQL Server – Fixing Varchar to Datetime Conversion Out-of-Range Error

SQL Server – How to Convert VARCHAR to DATETIME Format

Related Question