Sql query to get RowCount and Size of tables in the database

Sometimes we might need to keep track of space utilized by each table in our database. Specially in case if application database grew unexpectedly over course of development and testing. 

We want to know the space used of each table without using Management Studio's table properties. 

Here is your query. Just replace msdb with your DatabaseName

SELECT  sc.name + '.' + t.NAME AS TableName,          p.[Rows],          ( SUM(a.total_pages) * 8 ) / 1024 AS TotalReservedSpaceMB, -- Number of total pages * 8KB size of each page in SQL Server          ( SUM(a.used_pages) * 8 ) / 1024 AS UsedDataSpaceMB,          ( SUM(a.data_pages) * 8 ) / 1024 AS FreeUnusedSpaceMB  FROM    msdb.sys.tables t          INNER JOIN msdb.sys.schemas sc ON sc.schema_id = t.schema_id          INNER JOIN msdb.sys.indexes i ON t.OBJECT_ID = i.object_id          INNER JOIN msdb.sys.partitions p ON i.object_id = p.OBJECT_ID                                              AND i.index_id = p.index_id          INNER JOIN msdb.sys.allocation_units a ON p.partition_id = a.container_id  WHERE   t.type_desc = 'USER_TABLE'          AND i.index_id <= 1  --- Heap\ CLUSTERED         --AND t.NAME='MYTableName' -- Replace with valid table name GROUP BY sc.name + '.' + t.NAME,          i.[object_id],i.index_id, i.name, p.[Rows]  ORDER BY ( SUM(a.total_pages) * 8 ) / 1024 DESC

Sample output as shown below,

-Thanks,
Kiran

Sql Job : Traffic Light Status - depending on last 3 execution results

Recently came across interesting requirement from business.
Sql jobs keeps loading/refreshing data for projects. If you are not showing last data refresh time in report then end users will not be having idea about data updates. They will start panicking saying reports not showing correct data and you cannot send update to all 1000 users that last refresh is successful.

So we wanted to show traffic light status (Green/Yellow/Red) for job executions on SharePoint site.

In case of any doubt End user can directly visit page and see the respective jobs Status.
Logic was

The logic is as per Business expectations.
Last 1 job failure/Successful àGreen.
Last 2 job failures à Yellow
Last 3 job failures à Red.

Below is the query for getting jobs status

WITH RunTotalTestData AS (       SELECT         CASE             WHEN JobExecutionNo = 1 THEN Run_Status * 100             WHEN JobExecutionNo = 2 THEN Run_Status * 10             WHEN JobExecutionNo = 3 THEN Run_Status * 1         END         RunStat,         k.JobName,         k.StartTime,         k.run_status,         k.ExecutionStatus       FROM (                   SELECT                     name AS JobName,                     CONVERT(datetime, CONVERT(char(8), run_date) + ' ' + STUFF(STUFF((LEFT('000000', 6 - LEN(run_time)) + CONVERT(varchar(6), run_time)), 3, 0, ':'), 6, 0, ':')) AS StartTime,                     DATEADD(MINUTE, DATEDIFF(MINUTE, '0:00:00', CONVERT(time, STUFF(STUFF((LEFT('000000', 6 - LEN(run_duration)) + CONVERT(varchar(6), run_duration)), 3, 0, ':'), 6, 0, ':'))), CONVERT(datetime, CONVERT(char(8), run_date) + ' ' + STUFF(STUFF((LEFT('000000', 6 - LEN(run_time)) + CONVERT(varchar(6), run_time)), 3, 0, ':'), 6, 0, ':'))) AS EndTime,                     CASE run_status                         WHEN 1 THEN 1                         ELSE 0                     END AS run_status,                     CASE run_status                         WHEN 0 THEN 'failed'                         WHEN 1 THEN 'Succeded'                         WHEN 2 THEN 'Retry'                         WHEN 3 THEN 'Cancelled'                         WHEN 4 THEN 'In Progress'                     END AS ExecutionStatus,                     instance_id,                     jh.job_id,                     ROW_NUMBER() OVER (PARTITION BY name ORDER BY run_date DESC, run_time DESC) AS JobExecutionNo                   FROM msdb.dbo.sysjobhistory jh                   INNER JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id                   WHERE step_id = 0 AND j.name LIKE ('%Daily%')               )k       WHERE JobExecutionNo < 4 ) SELECT JobName,   CASE     WHEN Last3Exec = 0 THEN 'Red'     WHEN Last3Exec < 10 THEN 'Yellow'     WHEN Last3Exec >= 10 THEN 'Green'   END Flag,   CASE     WHEN Last3Exec = 0 THEN 2     WHEN Last3Exec < 10 THEN 1     WHEN Last3Exec >= 10 THEN 0   END FlagVal,   CASE     WHEN Last3Exec >= 10 THEN 'Job Running all Good'     ELSE 'Job is running very slow due to Performance Issue.'   END AS DisplayMessage,   Last3Exec FROM (SELECT a.JobName,SUM(a.RunStat) AS Last3Exec FROM RunTotalTestData a GROUP BY a.JobName) K GO

It will display Result-set as below,

-Thanks,

Kiran Jagtap.

Actual vs Effective space in Teradata

Just imagine even after having 100 GB free you get an error,"No more room in database". Strange, isn't it? 

In Teradata, even after having free space in your database it might get this error"No more room in database". Reason behind is Actual vs Effective Database space for table. Cause of Skew Factor table can take more space than its size. 

e.g. In administrator for any table it will show 5 gb  as Actual Space(Current Perm) but it might take 10gb as Effective Space from your database. So even if it looks that you have ample amount free space you might be running out of space.

Below is the query for finding of actual vs Effective space along with WasteSpace and Skew factor.

SELECT DatabaseName, TableName                   ,SUM(CurrentPerm) /1024**3  AS ActualSpaceGB ,MAX(CurrentPerm)*(HASHAMP()+1) /1024**3  EffectiveSpaceGB ,EffectiveSpaceGB-ActualSpaceGB  AS WasteSpaceGB ,100*((EffectiveSpaceGB-ActualSpaceGB)/ EffectiveSpaceGB) AS "WasteSpace%" ,(100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100)) AS SkewFactor FROM DBC.TableSize WHERE DatabaseName='DatabasName' GROUP BY 1,2 ORDER BY 5 DESC

Revisit and redefine your indexes and your issue will be resolved. :)

-B.regards,

Kiran

Insert Value into Identity Column Table

Sometimes we want to add value in Identity Column. Mostly when we forget to add Unknown member into dimension table.

Nothing to worry, you can add value to identity column very easily

Set identity_insert TableName on

Insert into TableName (id,data)values(-1000,'Unknown')

SEt identity_insert TableName off

Quick Reference: SSIS in 32- and 64-bits

 Quick Reference: SSIS in 32- and 64-bits

There are quite a few misconceptions flying about out there regarding SQL Server Integration Services and 64 bitness.  I've had to set more than a few people on the right path in the forums - mostly on one particular setting inside the SSIS packages that is getting misinterpreted.  Of course, this information only applies to 64-bit architectures - if you are running a Windows 32-bit OS, you have no choice - your packages will always run in 32-bit mode.

Why would you want to run in 32-bit mode if you have a 64-bit system?  Drivers, mostly.  I'm not referring to hardware drivers, but data providers.  As an example, Excel and some versions of Oracle don't have 64-bit providers.  So for the ever-increasing base of 64-bit users, here's the skinny on executing Integration Services packages in your choice of 64-bit or 32-bit mode.

Background

I Can Design Fine, Why Won't It Run?

A little background to start.  Business Intelligence Design Studio (BIDS) is a 32-bit application.  When you're designing your package, you're using 32-bit facilities - and have no choice in the matter.  When you execute your package using DTExec, you have the option of 32-bit or 64-bit operation - but the default on a 64-bit installation is to use 64-bit mode (obviously).  However, some commonly used objects in SSIS don't have 64-bit counterparts, and will therefore cause your packages to fail.

Unfortunately, it usually doesn't say anywhere in these messages that the fault lies with 32 vs 64 bits.  It's usually something like:

·         0x80040154 ... Class Not Registered

·         The AcquireConnection method call to the connection manager XXX failed with error code 0xC0202009

·         0xC00F9304 ... SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED

·         The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered

(I include those sample errors here in the hope that those searching the web may find this article!)

Why Do I Want 32-bit Mode?

The most common reason to want 32-bit mode in an executing SSIS package is the Excel Provider.  It's currently not available for 64 bits, and will cause your package to crash.  (Office 14 (2010) is reported to have 64-bit support - even though it's not supported side-by-side with 32-bit.)  This applies to the other Office providers as well - Access, specifically - and to several other third party drivers and providers (like Oracle).  They simply will not work in a 64-bit environment (pre-2010).  You may also wish to run Execute DTS 2000 Package Tasks - and those can only run in 32-bit mode as well.

It Depends How You're Executing Your Package

There are many ways to execute an SSIS package - and this is the primary determiner of whether you're running it in 64-bit or 32-bit mode.  So pick your execution environment from the list below, and read up on how to force the bitness you desire.

Choosing Bitness Inside Business Intelligence Development Studio (BIDS)

If you're running your package inside BIDS, the setup is simple unless you're using the Execute Package Task or Execute Process Task to run child packages.

The package you currently have open will (by default) run in 64 bit mode.  The setting that controls this is a property on the project called Run64BitRuntime.  To access this property, right-click on the Integration Services project in your solution explorer and select Properties.  Then select the Debugging node in the editor.  The default here is "true", which means all the packages in this project will run in 64-bit mode.  If you change this to "false", all the packages will be run in 32-bit mode.

Special Note: Execute Package Task

Any child packages executed via the Execute Package Task will run in the same mode as the parent, regardless of the Run64BitRuntime setting of the project that the child package belongs to, regardless of the setting of ExecuteOutOfProcess.  This means that even if your child package has Run64BitRuntime set to false in the project you designed it in, it will be executed in 64-bit mode within BIDS if your parent package's Run64BitRuntime property istrue.

Special Note: Execute Process Task

The Execute Process Task can allow you to choose 32-bit mode independently of the settings in the parent package, at the expense of running the child package in another process.  As with the SQL Agent methods described later, you can specifically identify the 32-bit DTExec to run SSIS child packages in 32-bit mode (see below).

Choosing Bitness With SQL Agent

Instructing SQL Agent what environment you want your packages to run in is simple in Integration Services 2008.  SSIS 2005 makes you jump through a few more hoops.

Integration Services 2008

In the Agent Job Step Properties, you'll be using the SQL Server Integration Services Package type of step.  If you go to the Execution Options tab, you'll see an option to "Use 32 bit runtime" down at the bottom.

Integration Services 2005

With SQL 2005, you can not use the Integration Services Package type of job step to run an SSIS package in 32-bit mode.  Your recourse is to use the Operating System type of job step, and refer to the 32-bit version of DTExec specifically in the command line that you use, and manually specify arguments to DTExec.

Hurdle #1 - Finding the 32-bit DTExec

Finding the executable shouldn't be difficult.  In a standard 64-bit installation, the 32-bit DTExec.EXE should be located in the "\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn" folder.  It's plainly called "DTExec.EXE", and doesn't identify itself in any way as a 32-bit app - you'll have to "know" it is by it being located in the 32-bit folder.  (Or you could try to execute it and watch Task Manager.)  If you've installed SQL to a non-standard location, you may have to hunt a little.  If you can't find it at all, you may not have installed the 32-bit components on your 64-bit machine.  During the install of SQL Server, if you only selected "Integration Services" and didn't install "Business Intelligence Development Studio" OR "Management Tools - Complete", then you won't have the 32-bit DTExec installed.  You'll have to run SQL Setup, and install one of those options.

Hurdle #2 - Determining the Command Line Arguments

Next, you need to determine the command line parameters you need to operate DTExec from the command line.  You could read through the documentation and attempt to determine the arguments and settings by yourself, but I would recommend you use the power of the included GUI tools.  Both the IS Job Step in SQL Agent, and the DTExecUI tool provide a GUI to configure an SSIS package run.  On the last page of the GUI, it very helpfully places the exact command line arguments needed to run DTExec, based on all of the configuration options you've chosen on the ten or so other tabs of the GUI.  Leverage that!  Set up your package execution using the GUI, then copy the arguments off that last page.

Precompiled Scripts

This is only an issue in Integration Services 2005 - the dev team completely fixed this issue in SSIS 2008.  There is a "Precompile" option on Script Tasks, which is set to "true" by default.  If this has somehow been set to "false", your packages may not execute in a 64-bit environment.

32-bit ODBC Drivers

There is also one other oddity with using 32-bit ODBC drivers in Windows - at least in Server 2003, 2008, Vista, and Windows 7 64-bit OSes.  The first step to using those drivers is to set up a DSN to handle storing the server name and other particulars.  In my experience, the natural first place to start is by opening the "Data Sources" applet in the Control Panel.  That's mistake #1 - because that applet only manages 64-bit drivers.  You won't see yours listed at all.  The next step is to poke around and realize that there's a "Data Sources (32-bit)" applet there in the Control Panel as well.  That's mistake #2 - but not your mistake.  In my experience, this icon leads to some odd hybrid 32/64 bit management utility.  It definitely did NOT manage my 32-bit sources.  If you fire it up, then look at the processes tab in Task Manager, you'll see a process labeled "odbcad32.exe"... but you'll notice that it doesn't have the "*32" after it denoting that it's a 32-bit app.  Whatever it's attempting to manage, it's not the 32-bit ODBC drivers.  What you need to do is navigate to another odbcad32.exe that's sitting in your SYSWOW64 folder.  That ODBC data source administrator truly does manage 32-bit drivers, in a 32-bit process.

References/Resources

Most (if not all) of this information is also distilled in an MSDN article: 64-bit Considerations for Integration Services.  If you find other useful articles, pointers, or mistakes in the above, please post a comment.

Some other very useful articles:

How To: Run a Package, MSDN

64-bit references within an SSIS Script Component by Michael Entin, Microsoft SSIS Dev.

64-bit Considerations for SQL Server Integration Services by Douglas Laudenschlager, Microsoft SSIS Dev.

Where is my app.config for SSIS? by Darren Green, SQL Server MVP.

Oracle Driver Configuration in a 64-bit environment by Rob Kerr

Importing data from 64-bit Excel in SSIS by Hrvoje Piasevoli

32- and 64-Bit Connectivity from the Same Machine by SQLCAT

Todd McDermid

SQL- Query to find Rows count,Table Size along with Columns Count in database

USE DatabaseName     --Replace Database Name

GO

CREATE TABLE #temp (          --Creating New Temp Table

table_name sysname ,

row_count INT,

reserved_size VARCHAR(50),

data_size VARCHAR(50),

index_size VARCHAR(50),

unused_size VARCHAR(50))

SET NOCOUNT ON

INSERT #temp                        --Insert Infomation into Temp Table

EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT a.table_name,

a.row_count,

COUNT(*) AS col_count,

a.data_size

FROM #temp a

INNER JOIN information_schema.columns b

ON a.table_name collate database_default

= b.table_name collate database_default

GROUP BY a.table_name, a.row_count, a.data_size

ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

DROP TABLE #temp              --Drop Temp Table

Dates, Times and Timestamps in Teradata

Dates, times, and timestamps in Teradata can be a little tricky. This document explains how to do various things that you may want to do. It is current through TD 12. Preliminaries The examples below use Teradata syntax, so they assume that you are running in BTEQ. If you are using SQL Assistant (Queryman), be sure to uncheckthe option box "Allow use of ODBC SQL Extensions in queries". Changing the Date Time Format in your ODBC data source to AAA or IIA may also help. datecol means a column defined as DATE. Defining columns and formats Times and timestamps can be defined with any number of decimal places from 0 to 6 (time(0), timestamp(6), etc.). As it turns out, however, MP-RAS on Intel doesn’t keep track of anything beyond 2 decimal places (hundredths of seconds), so in many cases it is useless to define columns with more than 2 decimal places. If, however, you are loading data from another server, that data could have digits down to the microsecond. The formats for all columns are described in the SQL Reference: Data Types and Literals, Chapter 9. In V2R5 and later releases, you can specify many different formats for time and timestamp fields. Dates Literals Date literals can be specified in many ways: -- in the same format as the column they are being compared to where infamy_date = '12/07/1941' -- with an explicit format where infamy_date = '07Dec1941' (date, format 'DDMMMYYYY') -- in ANSI standard form (must be preceded by the keyword DATE) where infamy_date = date '1941-12-07' -- in numeric form (not recommended) where infamy_date = 411207 Observe in the examples above that the word DATE must be used to specify the data type. To get the system date, therefore, CURRENT_DATE is a better choice than DATE. Number of days between two dates datecol - datecol will return the number of days between two dates. select date '2003-08-15' - date '2003-01-01'; (2003-08-15-2003-01-01) ----------------------- 226 Adding or subtracting months Generally speaking, you should use the ADD_MONTHS function to add months to a date (or to subtract months). Your project may require adding a number of days, but if calendar months are required, ADD_MONTHS is the way to go. select add_months(current_date, 3); ADD_MONTHS(Date, 3) ------------------- 2005-10-19 select add_months(current_date, -2); ADD_MONTHS(Date, -2) -------------------- 2005-05-19 /*** Last day of the month is still the last day ***/ select add_months(date '2002-01-31', 1); ADD_MONTHS(2002-01-31, 1) ------------------------- 2002-02-28 Computing the day of the week Computing the day of the week for a given date is not easy in SQL. If you need a weekday, I recommend that you look it up in the view sys_calendar.calendar (or join to it), thus: select day_of_week from sys_calendar.calendar where calendar_date = date '2003-05-01'; day_of_week ----------- 5 [i.e. Thursday] Computing the first day of a month select datecol - extract(day from datecol) + 1 This subtracts the number of days since the beginning of the month, taking you to "day 0", or the day before the first of the month; then adds 1. Computing the last day of a month select add_months((datecol - extract(day from datecol)+1),1)-1 Same idea, but this computes the first day of the following month and then subtracts 1. Times Literals The easiest way to specify a time literal is in ANSI form with a 24-hour clock: time '15:09:17' But in V2R5 you can use another format as long as you tell Teradata what it is: '03-09-17PM' (time, format 'HH-MI-SST') Changing floats to times Some Data Dictionary tables have time columns that are defined as FLOAT rather than TIME. Here’s how to convert them to TIMEs, believe it or not: select cast(cast(cast(TimeFld as format '99:99:99.99') as char(11)) as time(6)) from DBC.EventLog ... Adding or truncating decimal places At first it would seem that these expressions should work: cast(time '01:02:33' as time(6)) /* adding */ cast(time '01:02:33.456' as time(0)) /* truncating */ Alas, the first one works but the second one does not: select cast(time '01:02:33' as time(6)); 01:02:33 --------------- 01:02:33.000000 select cast(time '01:02:33.456' as time(0)); *** Failure 7454 DateTime field overflow. Truncating decimal places can be done in a UDF, but in SQL you must convert the time to a character string and back again: select cast(cast(time '01:02:33.456' as char(8)) as time(0)); 01:02:33.456 ------------ 01:02:33 CURRENT_TIME, however, is an exception: select cast(current_time as time(0)); Current Time(0) --------------- 15:20:29 Time differences First we shall address the case where your time data is defined as a number (FLOAT or DECIMAL, perhaps) in hhmmss form. select ((time02 / 10000) * 3600 + (time02 / 100 MOD 100) * 60 + (time02 MOD 100)) - ((time01 / 10000) * 3600 + (time01 / 100 MOD 100) * 60 + (time01 MOD 100)) as time_diff from dttest; time_diff ------------- 6432. [in seconds -- about 1.8 hours] If the earlier time could fall on one day and the later time on the next day, you may have to add 86,400 (the number of seconds in one day) to the later time, like so: select case when time02 >= time01 then ((time02 / 10000) * 3600 + (time02 / 100 MOD 100) * 60 + (time02 MOD 100)) - ((time01 / 10000) * 3600 + (time01 / 100 MOD 100) * 60 + (time01 MOD 100)) else /*** Midnight has passed ***/ (((time02 / 10000) * 3600 + (time02 / 100 MOD 100) * 60 + (time02 MOD 100)) + 86400) - ((time01 / 10000) * 3600 + (time01 / 100 MOD 100) * 60 + (time01 MOD 100)) end as time_diff from dttest; time_diff ------------- 18094. Next we consider the case where your time data is defined as TIME(n). The usual way to take the difference of two times would be as follows: select time02 - time01 hour(2) to second from dttest; (time02 - time01) HOUR TO SECOND -------------------------------- 10:39:23.000000 The above result is an interval (INTERVAL HOUR TO SECOND, to be exact). If, however, you want to compute the difference in seconds, as above, you again have to split the times up: select (extract(hour from time02) * 3600 + extract(minute from time02) * 60 + extract(second from time02)) - (extract(hour from time01) * 3600 + extract(minute from time01) * 60 + extract(second from time01)) as time_diff from dttest; time_diff ----------- 38363 /*** After midnight ... ***/ select case when time02 >= time01 then (extract(hour from time02) * 3600 + extract(minute from time02) * 60 + extract(second from time02)) - (extract(hour from time01) * 3600 + extract(minute from time01) * 60 + extract(second from time01)) else (extract(hour from time02) * 3600 + extract(minute from time02) * 60 + extract(second from time02) + 86400) - (extract(hour from time01) * 3600 + extract(minute from time01) * 60 + extract(second from time01)) end as time_diff from dttest; time_diff ----------- 38363 Timestamps Literals The easiest way to specify a timestamp literal is in ANSI form: timestamp '1994-12-25 23:46:29' But in V2R5 you can use another format as long as you tell Teradata what it is: '12/25/1994 11:46:29PM' (timestamp, format 'MM/DD/YYYYBHH:MI:SST') Extracting the date or time portion of a timestamp Extract the date or time portion of a timestamp thus: select cast(ts01 as date) from dttest; ts01 -------- 04/07/27 select cast(ts01 as time(6)) from dttest; ts01 --------------- 10:24:37.739920 Adding or truncating decimal places As in the case of time fields, you can add decimal places but cannot truncate them: select cast(timestamp '2008-06-24 01:02:33' as timestamp(6)); 2008-06-24 01:02:33 -------------------------- 2008-06-24 01:02:33.000000 select cast(timestamp '2008-06-24 01:02:33.456' as timestamp(0)); *** Failure 7454 DateTime field overflow. As before, truncating requires converting the timestamp to a character string and back again: select cast(cast(timestamp '2008-06-24 01:02:33.456' as char(19)) as timestamp(0)); 2008-06-24 01:02:33.456 ----------------------- 2008-06-24 01:02:33 CURRENT_TIMESTAMP is not an exception: select cast(current_timestamp as timestamp(0)); *** Failure 7454 DateTime field overflow. select cast(cast(current_timestamp as char(19)) as timestamp(0)); Current TimeStamp(6) -------------------- 2008-09-19 15:27:36 Combining date and time to make a timestamp Thanks to Dieter Nöth for this tip. The most efficient way to combine a date and a time is to cast the date to a timestamp and add the time. But you can’t simply add a time, because TIME is a point in time and not a duration. So you have to transform it into an interval: cast(Date_column as TimeStamp(6)) + ((Time_column - time '00:00:00') hour to second(6)) Length of time between two timestamps You can subtract one timestamp from another. The result will be an interval, and you must specify a precision for the interval, like so: select ts02 - ts01 day(4) to second(6) from dttest; (ts02 - ts01) DAY TO SECOND --------------------------- 371 05:26:35.639649 select ts01 - ts02 day(4) to second(6) from dttest; (ts01 - ts02) DAY TO SECOND --------------------------- -371 05:26:35.639649 You can also convert this interval to seconds or minutes like so: /*** Difference in seconds ***/ select (ts02 - ts01 day(4) to second) as tsdiff, (extract(day from tsdiff) * 86400) + (extract(hour from tsdiff) * 3600) + (extract(minute from tsdiff) * 60) + extract(second from tsdiff) as sec_diff from dttest; tsdiff sec_diff --------------------- ----------------- 371 05:26:35.639649 32073995.639649 /*** Difference in minutes ***/ select (ts02 - ts01 day(4) to minute) as tsdiff, (extract(day from tsdiff) * 1440) + (extract(hour from tsdiff) * 60) + extract(minute from tsdiff) as min_diff from dttest; *** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. tsdiff min_diff ----------- ----------- 371 05:26 534566 Number of days between two timestamps If you just want the number of days between two timestamps and wish to ignore the time portion, either of the following two techniques will work, but note the differences. The first technique lops off the time portion of each timestamp, so it will be equivalent to subtracting the two days; the result is an integer. The second seems to do the same but returns an interval. If you use the second technique, be sure to allow enough digits for DAY. sel cast(ts02 as date) - cast(ts01 as date) from dttest; (ts02-ts01) ----------- 371 -- [type of this result is INTEGER] sel ts02 - ts01 day(4) from dttest; (ts02 - ts01) DAY ----------------- 371 -- [type of this result is INTERVAL DAY] Other operations on timestamps Operand 1 Operator Operand 2 Result Type   Timestamp     + or -     Interval     Timestamp     Interval     +     Timestamp     Timestamp     Interval     + or -     Interval     Interval     Interval     * or /     Numeric     Interval     Numeric     *     Interval     Interval   Intervals Literals There are many types of intervals; see the Data Types manual for details. The key point to remember is that an interval type cannot span months, because not all months are the same length. /* 1 year, 2 months, 5 days? */ select interval '1-02-05' year to day; *** Failure 3706 Syntax error: Invalid INTERVAL type definition. select interval '05 01:02:03' day to second; 5 01:02:03 ----------- 5 01:02:03 The fields in intervals are smallints, so the precision cannot be greater than 4. select interval '1234' day; 1234 ----- 1234 select interval '12345' day(5); *** Failure 3706 Syntax error: Invalid INTERVAL Literal. Casting data to intervals If you need to convert, say, a smallint in a table to an interval, you can cast it like so: cast(smallint_field as interval second(4)) cast(smallint_field as interval minute(4)) You can also convert combinations of fields to longer intervals by first combining them into properly formatted character strings, then casting those to intervals. cast( cast( (smallint_hours (format '999:')) || (smallint_minutes (format '99')) as char(7) ) as interval hour to minute ) Other operations with intervals Here are some examples of the other operations mentioned in the table above. /******** Timestamp + Interval = Timestamp ********/ select timestamp '2008-08-15 10:11:12' + interval '5:00:39' hour to second; (2008-08-15 10:11:12+ 5:00:39) ------------------------------ 2008-08-15 15:11:51 /******** Timestamp - Interval = Timestamp ********/ select timestamp '2008-08-15 10:11:12' - interval '5:00:39' hour to second; (2008-08-15 10:11:12- 5:00:39) ------------------------------ 2008-08-15 05:10:33 /******** Interval + Timestamp = Timestamp ********/ select interval '5:00:39' hour to second + timestamp '2008-08-15 10:11:12'; ( 5:00:39+2008-08-15 10:11:12) ------------------------------ 2008-08-15 15:11:51 /******** Interval + Interval = Interval ********/ select interval '5:00:39' hour to second + interval '2:10:11' hour to second; ( 5:00:39+ 2:10:11) ------------------- 7:10:50 /* The interval types can be different. */ select interval '5' hour + interval '10' minute; ( 5+ 10) -------- 5:10 select interval '5:03' hour to minute + interval '14:12' minute to second; ( 5:03+ 14:12) -------------- 5:17:12 /* But, as always, you cannot span months. */ select interval '2' month + '5' day; *** Failure 3706 Syntax error: Invalid DateTime value expression. /******** Interval - Interval = Interval ********/ select interval '5:00:39' hour to second - interval '2:10:11' hour to second; ( 5:00:39- 2:10:11) ------------------- 2:50:28 select interval '5' hour - interval '10' minute; ( 5- 10) -------- 4:50 select interval '5:03' hour to minute - interval '14:12' minute to second; ( 5:03- 14:12) -------------- 4:48:48 /******** Interval * Numeric = Interval ********/ select interval '5' hour * 2; ( 5*2) ------ 10 select interval '4:05:06' hour to second * 3; ( 4:05:06*3) ------------ 12:15:18 select interval '1 02' day to hour * 4; ( 1 02*4) --------- 4 08 /******** Interval / Numeric = Interval ********/ select interval '5' hour / 2; ( 5/2) ------ 2 select interval '4:05:06' hour to second / 3; ( 4:05:06/3) ------------ 1:21:42 select interval '1 02' day to hour / 4; ( 1 02/4) --------- 0 06 /******** Numeric * Interval = Interval ********/ select 2 * interval '5' hour; (2* 5) ------ 10 select 3 * interval '4:05:06' hour to second; (3* 4:05:06) ------------ 12:15:18 select 4 * interval '1 02' day to hour; (4* 1 02) --------- 4 08