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
Average of multiple columns
I have a table called Request and the data looks like: Req_ID R1 R2 R3 R4 R5 R12673 2 5 3 7 10 R34721 3 5 2 1 8 R27835 1 3 8 5 6 Now I want …
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 …
Delete all records except the most recent one?
I have two DB tables in a one-to-many relationship. The data looks like this: Resultset: I want to delete all applications except for the most recent one. In other words, each student must only have one application linked to it. Using the above example, the data should look like this: How would I go about constructing my DELETE statement to
Best practices of structuring stored procedures
As a developer mainly writing c# I have adopted some good practices when writing c# code. When I sometimes write stored procedures I have trouble applying those practices to the stored procedure code. …
How to alter a column’s data type in a PostgreSQL table?
Entering the following command into a PostgreSQL interactive terminal results in an error: What is the correct command to alter the data type of a column? Answer See documentation here: http://www.postgresql.org/docs/current/interactive/sql-altertable.html
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