I need to convert a DateTime type value to BIGINT type in .Net ticks format (number of 100-nanosecond intervals that have elapsed since 12:00:00 midnight, January 1, 0001). The conversion should be perform in Sql server 2008 using T-SQL query For example: will convert to: Answer I have found a CodeProject article that may assist: Convert DateTime To .NET Ticks
Tag: sql-server
How to zero out all negative numbers in a group-by T-SQL statement
I have a T-SQL query where I want all negative quantities to be zeroed out. SELECT p.productnumber, v.[Description], SUM(i.Quantity) as quantity FROM … LEFT JOIN … LEFT JOIN … LEFT JOIN … …
SQL query to get the precision value of a column
I need a sql query that gets the precision value for certain columns.. I am mainly concerned with a decimal type column and I need the precision value for the same. I realise that it is possible to do so in certain versions and vendors of database servers. It would be nice if you could list down for a few
Creating and Selecting table variables dynamically in SQL Server stored procedure?
Please guide me how to create table variables dynamically. I know it can be like this: How I will create if dont know columns and data types. Scenario is I have to create table variable as per a physical tables and selct data into them, use them in SP and then return data in table variables to C# program. For
Drop a temporary table if it exists
I have two lines of code in SQL that create two tables on the fly, i need to do something like IF TABLE EXISTS DROP IT AND CREATE IT AGAIN ELSE CREATE IT my lines are the following ones …
Return sql rows where field contains ONLY non-alphanumeric characters
I need to find out how many rows in a particular field in my sql server table, contain ONLY non-alphanumeric characters. I’m thinking it’s a regular expression that I need along the lines of [^a-zA-Z0-9] but Im not sure of the exact syntax I need to return the rows if there are no valid alphanumeric chars in there. Answer SQL
SQL Server Stored Procedure to Send Email
This is my first attempt at writing a stored procedure that emails someone. When trying to execute I get these errors: The code that I am using which is causing this is: Answer You’re missing a comma after the @body line, which is throwing off your declarations. Add it here:
Group by every N records in T-SQL
I have some performance test results on the database, and what I want to do is to group every 1000 records (previously sorted in ascending order by date) and then aggregate results with AVG. I’m actually looking for a standard SQL solution, however any T-SQL specific results are also appreciated. The query looks like this: Answer Something like that should
Is there a solution for getting the default value of the parameters of a given stored procedure?
I use INFORMATION_SCHEMA.PARAMETERS for getting information about a stored procedure parameter now. I need to know the default values of parameters. Is there a solution for getting the default value of the parameters of a given stored procedure? Answer Parse the SQL code if you are doing it via SQL commands… The information isn’t stored in a system table. From
Display a ConnectionString dialog
I’m trying to create a program in C# that should be able to create, backup and restore a SQL Server database. For this, the user needs to be able to setup a connection string to the desired SQL Server (and database). I would like to use the same dialog as for example Visual Studio for creating the connection string. Is