Get person's age at a given moment using T-SQL and read only access

I thought SQL Server's DATEDIFF was a neat function to calculate a person's age at a given moment. But I was wrong.

It's important that I get the correct age of a person at a given time comparing that time's date to the date of birth. I'm given read only access. This means I can't create a function, so I have to implement it in the query.

So far came up with CAST(DATEDIFF(day, dateOfBirthColumn, otherDateCoumn) / 365.25 AS Integer) AS Age. This gives me a pretty exact age, but not the correct age in some cases due to the fact that the division by 365.25 isn't actually correct.

Any suggestions on how to calculate the exact age between two dates?

I'm querying a SQL Server 2005 database. Maybe unneccesary to say, but why didn't the engineers at MS thought of this...?

Checking if data violates SQL decimal constraint

I have a table that is getting a column changed from a VARCHAR to DECIMAL(20,7) data type. I need find out what rows have a value in the column that violates the DECIMAL(20,7) data type and I believe I am close.

What I have so far is this:

SELECT * 
FROM tableName
WHERE 
     (ISNUMERIC(columnName)  = 0 and columnName IS NOT NULL)
     OR (columnName LIKE '%,%')
     OR (LEN(columnName) > 20)

I think I am mostly hung up on the the LEN check, as I can still miss results. Is there a way to get deeper into the length so I can check with precision, instead of just a length of 20?

SQL Server 2005 Backups

The place I work at right now has no backups. Each server supposedly has a snap shot every 20 minutes or so and replicated to a backup SAN.

I know I need backup for easy restore, etc, etc... but I would like to hear more reasons for having .bak files instead of the SAN admin telling me he can get me the .mdf and .ldf files. Give me some fire power.

I'm also testing SQL Safe for backup compression and speed for SQL Server 2005 so any information on a tool like that would help too.

How to calculate SQL server stored procedure execution time

I have a stored procedure. I want to check how much time it takes to execute. Please note that I don't have access to profiler. I am using SQL server 2005

I tried using the following batch query

SELECT CONVERT( VARCHAR(24), GETDATE(), 121)
EXEC cif_query_trans_by_card_Final2 '0060F423F28A6095AD070CEFFD7E81D245D7D0CD7B', '11223345'
SELECT CONVERT( VARCHAR(24), GETDATE(), 121)

But I am getting same value in both timestamp as 2014-12-30 18:09:52.653

Any help will be greatly appreciated.

Thanks in advance.

SQL max concurrent sessions per hour of day

I work in a healthcare call-centre environment. I have access to our core system's database and I want to use the data in it to determine the number of staff logged on each hour of the data in a given period that may expand over 24 hours (e.g. 24/12/2014 - 26/12/2014).

There is a userlog table that records when users log on and log off.

userlog table example

UserRef                                 Date                    LogType SessionID   
--------------------------------------- ----------------------- ------  -----------------------
96AD647C-D061-43F5-9F8D-FA6C74817E07    2002-10-17 14:11:25.763 LOGON   8D451569-0260-46BB-9B9E-F49B3E778161    
96AD647C-D061-43F5-9F8D-FA6C74817E07    2002-10-17 18:11:32.547 LOGOFF  8D451569-0260-46BB-9B9E-F49B3E778161    
60738820-5F72-4E20-A070-57E07C83B6DE    2002-10-17 14:53:31.153 LOGON   C773894C-8B2D-4054-A550-3F04B4C5669F    
60738820-5F72-4E20-A070-57E07C83B6DE    2002-10-17 22:55:25.607 LOGOFF  C773894C-8B2D-4054-A550-3F04B4C5669F
90A55FDD-967E-4D99-96DF-96840CDB2CDF    2002-10-17 15:26:40.123 LOGON   1CE5F5A5-4E20-4D4A-BB67-EB0CB33976D7    
96AD647C-D061-43F5-9F8D-FA6C74817E07    2002-10-17 15:51:28.590 LOGON   7EFDEE1C-15CF-4DE1-B59F-7AFC49B4BE73    
90A55FDD-967E-4D99-96DF-96840CDB2CDF    2002-10-17 15:58:05.217 LOGOFF  1CE5F5A5-4E20-4D4A-BB67-EB0CB33976D7    
96AD647C-D061-43F5-9F8D-FA6C74817E07    2002-10-17 15:58:31.013 LOGOFF  7EFDEE1C-15CF-4DE1-B59F-7AFC49B4BE73    
90A55FDD-967E-4D99-96DF-96840CDB2CDF    2002-10-17 15:58:32.733 LOGON   03F56AB8-FED5-4CC7-8445-26BF55F58E60    
90A55FDD-967E-4D99-96DF-96840CDB2CDF    2002-10-17 16:13:02.827 LOGOFF  03F56AB8-FED5-4CC7-8445-26BF55F58E60    

Desired results (this is not intended to reflect the above sample data):

Date         Hour    Number of users logged in
----         ----    -------------------------
01/12/2014    0                   0
01/12/2014    1                   0
01/12/2014    2                   0 
01/12/2014    3                   0
01/12/2014    4                   0
01/12/2014    5                   1
01/12/2014    6                   1
01/12/2014    7                   1
01/12/2014    8                   3
01/12/2014    9                   7
01/12/2014    10                  7
...                 
01/12/2014    23                  0

To be clear: I'm trying to get the hour to still display with a 0 count when there were no users logged in.

I guess what I'm looking for is the maximum concurrent sessions for each hour of the day, but I'm not overly technical or skilled in SQL (getting better bit by bit, though!) so I hope that terminology doesn't confuse things!

I've googled for this and found a few similar scenarios, but for Oracle and MySQL, or where the log table records the logon data differently. I'm sure I'll get to a point where I can successfully 'translate' other database query code to MS SQL, but I'm not there yet!

I am using: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86).

Thanks in advance for your help.

SQL Server GenRepeatingChars?

On my Sql Server Query i have found a query where there is a statement.

" dbo.GenRepeatingChars('0',8) AABBCCDD "

here AABBCCDD is may table column name. but What is the use of GenRepeatingChars? Please can any one brief me in details...

Calculating due date using business hours and holidays

I need to calculate due date / end date for SLAs. As input values I have the start date and a timespan (in minutes). This calculation needs to take into account business hours, weekends, and holidays.

I've seen a lot of examples where the input is start date and end date, but have been struggling finding anything similar to the above input values.

Is there an elegant solution to this problem? Is there a way to calculate due date without using a loop? I can't think of a way to do the calculation without doing something similar to the following terrible algorithm:

1. Create a return variable "due date" and set it to input variable "start date"


2. Create a control variable "used minutes" and set it to 0


3. Create a loop with the condition "used minutes" <= "input timespan"


4. Inside the loop, add a second to the "due date" return variable


5. Inside the loop, check if the second is within hours of operation (checking business hours, weekends, and holidays). If so, increment control variable "used minutes" by 1.


6. Upon exiting the loop, return variable "due date"