CONVERT_TO_DATE function

Posted on by madhivanan

Often I see people asking this question “Sometimes clients give date values as integers, float or formatted string values. How should we handle this and store them properly and query on it?”

For example October 04, 2010 can be expressed one of the following ways (the number format is dmy)

04102010
04102010.0
041010
4102010
04-oct-2010
04/10/2010
04.10.2010
Oct 4, 2010

In this case the correct way is to convert all of these into proper DATE value and store/use them. You can use the following function

Create function convert_to_date (@date_val varchar(25))
returns datetime
as Begin
Select @date_val=
case when @date_val like '%.0%' then substring(@date_val,1,charindex('.',@date_val)-1)
else @date_val
      end
return
cast(
      case when @date_val like '%[a-zA-Z-/]%' then
            case when ISDATE(@date_val)=1 then @date_val else NULL end
      when len(@date_val)=8 then
            right(@date_val,4)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2)
      when len(@date_val)=7 then
            right(@date_val,4)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1)
      when len(@date_val)=6 then
            case when right(@date_val,2)<50 then '20' else '19' end
            +right(@date_val,2)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2)
      when len(@date_val)=5 then
            case when right(@date_val,2)<50 then '20' else '19' end
            +right(@date_val,2)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1)
else
            case when ISDATE(@date_val)=1 then @date_val else NULL end
end
as datetime
)
END


Now execute and see how these are converted to proper date values

select
dbo.convert_to_date('04102010'),
dbo.convert_to_date('04102010.0'),
dbo.convert_to_date('041010'),
dbo.convert_to_date('4102010'),
dbo.convert_to_date('04-oct-2010'),
dbo.convert_to_date('10/04/2010'),
dbo.convert_to_date('10-04-2010'),
dbo.convert_to_date('Oct 4,2010'),
dbo.convert_to_date('04 oct ,2010'),
dbo.convert_to_date('October 04 2010')

 
All the above give the output 2010-10-04 00:00:00.000

So you can make use of this function to convert the string values into proper DATETIME value

Guest Post – Get rid of Database problems with Stellar Phoenix SQL Database Repair

Posted on by madhivanan

One of my friends Priyanka Chauhan has expressed her willingness to have a Guest post here.

She  is a technical writer in “Stellar Data Recovery“ with 5 years of experience and has written several articles on SQL. She has the fine knowledge of SharePoint and SQL Server. In the spear time she loves reading and gardening.

In this post, She writes on how to Get rid of Database problems with Stellar Phoenix SQL Database Repair

If you’re an SQL Database Administrator (DBA), you have a lot riding on your shoulders. From database management, to user account handling to fixing issues, it’s all in your hands. As such, you need to know the right tools that can help you in your different tasks. After all, even the best of us need a hand at times!

Any experienced DBA can vouch for the fact that corruption is very common with an SQL database. Moreover, the reasons behind SQL database corruption can vary from a small error to a major issue. And since SQL database corruption is so volatile, most DBAs try out countless tricks and test many tools to come up with solutions to this problem.

In this article, we will be discussing about one tool that has made the life of DBAs quite easy with its utility. It is Stellar Phoenix SQL Database Repair and as its name suggests, this software fixes SQL databases post corruption. But before going into the details of this software, lets have a brief introduction to the problem.

Corruption of SQL Database

Before scouting for the solution to a problem, one must understand that it cannot be solved if the root cause is not eliminated. There can be several reasons behind SQL database corruption:

  • Bugs in driver or firmware
  • Anti-virus software
  • Attempts to compress database files
  • Bugs within the database
  • Failed database version upgrades

Once the reason behind corruption is pinpointed, the next step is identifying the kind of corruption that resulted due to that reason. Yes, SQL database corruption can be of many types. Have a look:

Metadata Corruption – Making improper changes to the sysindexes, sysobjects and syscolumns tables can make them inconsistent. This kind of corruption can be highlighted by the DBCC CHECKDB utility.

Page-level Corruption – An SQL database is often viewed as a set of pages containing data critical to maintaining database consistency. Damage to any of these pages results in Page-level corruption.

Database Table Corruption – Sometimes, corruption in an SQL database is not directly identified. It comes out only when you scan or access a table or query for specific data.

Index Corruption – This type of corruption can be divided into Clustered and Non-Clustered depending upon redundant data being the problem or the clustered indexes in the base data.

MDF File Corruption – MDF files contain the database schema information and this corruption results primarily from server crashes or hardware failure.

Repairing SQL Database using Stellar Phoenix SQL Database Repair

Be it any type of corruption, all it takes is a great database repair tool to get over it. After trying out various manual and automatic techniques, most DBAs claim to achieve successful database repair with Stellar Phoenix SQL Database Repair. And they have good reason to believe so.

Stellar Phoenix SQL Database Repair does a commendable job of repairing damaged or corrupt MS SQL databases. This intuitive software fixes all issues accurately and recovers inaccessible objects in MDF and NDF files. It preserves database integrity by exercising the highest level of non-destructive repair on a damaged / corrupted MS SQL database. Just one run of this brilliant software is enough to realize how it can change a DBA’s world around by solving one of his most irritating problems – database corruption.

Here are the software’s striking features:

  • Restores corrupted databases by repairing even severely damaged MDF and NDF files
  • Restores all database components like tables, procedures, triggers, views, rules, user defined data types etc
  • It can self-reconnect in case the repair process is disrupted due to any reason
  • It has the ability to perform restore of SQL Server PAGE-compressed and ROW-compressed data
  • Enables selective recovery of database objects
  • A distinctive feature of this software is that it offers a preview of database objects before recovering them. After scanning the entire database for recoverable objects, it displays them in a tree-like structure from which the user can choose the objects he wants to preview.
  • Gives the user an option to save recovered database at a desired location after completion of SQL recovery process.
  • An easy to use interface makes working with the software extremely smooth.
  • A huge advantage of the tool is its compatibility with a wide range of MS SQL Server versions including 2014, 2012, 2008 R2, 2008, 2008*64, 2008 Express, 2005, 2005*64, Express, 2000, 2000*64, 7.0.

Here are the steps in which this software works:

  • Download and install Stellar Phoenix SQL Database Repair
  • Run the software. If the MS SQL Server is running simultaneously, the software will prompt you to stop it and copy the database to a different location. Click on Ok to dismiss the pop-up and stop the running server.
  • In the next window, click on the “Select Database” button to select the corrupted database (An awesome feature of this software is that it realizes that users might not be aware of the location of the SQL database file. Hence an option to “Find” the file is provided just below the “Select Database” section).
  • Once the database to be repaired has been selected, click on “Repair” button at the bottom of the software window.
  • The software will now make use of an advanced algorithm to search for all recoverable objects within the selected file and display them within the left-hand side panel. You can preview any item you want to here.
  • Next enter your authentication details and select the destination where you want to store the repaired database.
  • Finally, click on “Connect” to save the repaired database at the location chosen by you.

In simpler words, it’s just a 3-step process: Select-Scan-Repair!

Final Words

Every DBA who has not yet tried out this helpful software has definitely been searching for a solution to SQL database corruption in the wrong place. The 3-step approach of Stellar Phoenix software makes it a definite winner in the field of SQL database repair.