Showing posts with label SQL Data Types. Show all posts
Showing posts with label SQL Data Types. Show all posts

Friday, November 18, 2011

Get the the difference between two dates in MS SQL

The DATEDIFF() function returns the difference between two dates
Syntax: DATEDIFF ( datepart , startdate , enddate )

Following is an example for the DateDiff()
DECLARE @startdate datetime ='2011-11-14 02:00:06.957';
DECLARE @enddate datetime = '2011-11-16 05:00:15.490';
SELECT DATEDIFF(day, @startdate, @enddate) as dayDifference,DATEDIFF(HOUR, @startdate, @enddate)as hourDifference

dayDifference hourDifference
2 51

Similarly the following are the available datepart Options on the DateDiff() Function.

year(or yy or yyyy)
quarter(or qq or q)
month (or mm or m )
dayofyear(or dy or y )
day (or dd, d )
week(or wk or ww)

hour(or hh)
minute(or mi or n)
second(or ss or s )
millisecond(or ms)
microsecond(or mcs)
nanosecond(or ns)
TZoffset(or tz)
ISO_WEEK(or isowk or isoww)


Friday, January 22, 2010

Converting Clob Datatype to String and ViceVersa .

If you have been using Hibernate 3.x.x or greater you can easily convert clob type to string type and vice versa, i.e String data type to Clob type.

Following piece of codes demonstrate the process of doing the conversion.


Clob textClobValue;
String textValue;
String newTextValue;
Clob newTextClobValue;
InputStream textStream;
Paragraph paragraph // my domain Class that contains a clob datatypes and used to update the database connection with hibernate connection (Hibernate version >=3.0)

textStream = textClobValue.getAsciiStream(); //return inputstream
newTextValue=textValue.replaceAll("Some text","new Text"); //Replacing some text from old text.


public static String convertStreamToString(InputStream is) throws Exception {
BufferedReader reader = new BufferedReader(new InputStreamReader(is));
StringBuilder sb = new StringBuilder();
String line = null;

while ((line = reader.readLine()) != null) {
sb.append(line + "\n");
return sb.toString();



 © 2006-2010