datediff function in sql

To achieve a simple business:Use the SQL script to get the ' 61970b0101.jpg ' part of the string ' large/020700/61970b0101.jpg '.The first thought is in C # LastIndexOf, but there is no such function in SQL, only the CHARINDEX function, we have to use the existing resources to solve the curve.Solution Ideas:1. Use the

In a select statement, only SQL functions can be used to operate fields (link to SQL server ), Select Field 1 from table 1 where field 1. IndexOf ("Cloud") = 1; This statement is incorrect because the indexof () function is not an SQL function. You can change it to the corre

Date_sub () Subtract a specified time interval from a date DATEDIFF () Returns the number of days between two dates Date_format () Display Date/time in a different format SQL Server Date functionThe following table lists the most important built-in date functions in SQL Server:

SQL date When processing a date, the most difficult task is to ensure that the format of the inserted Date matches the format of the date column in the database. As long as the data contains only the date part, running the query will not cause problems. However, the time is complicated. Before discussing the complexity of date queries, let's take a look at the most important built-in date processing functions.MySQL Date

Function Use Northwind Go CREATE FUNCTION Getstar (@ datetime) RETURNS varchar (100) As BEGIN --just one sentence of SQL Return ( --declare @ datetime --set @ = getdate () Select Max (Star) From ( --the constellation, the starting date of the constellation, the day of the constellation start Select ' Capricorn ' as star,1 as [month],1 as [day] UNION ALL select '

Solution for slow SQL query caused by custom functions, function SQL During the report process, one report queries the exchange rate of the corresponding currency, and the exchange rate is in another table, the condition for querying the exchange rate is to query the exchange rate of the previous month of the statistical date in the Exchange Rate Table based on

I saved a tree structure in the department table of the database and looked up all parent departments when the sub-departments are known. the written function is as follows quot; CREATEFUNCTIONqueryAllDept (@ currdeptidvarchar (50 )) returnsvarchar (200) BEGINdeclare @ allnamevarchar (200) DEFAULT #39; #39; declare @ tempnamevarchar (200) DEFAULT #39; #39 ;; declare @ tempparidvar mysqlsql function loo

aggregate function sum indicates how you want to handle the value of the converted column, whether it is the sum (sum), average (avg), Min,max, and so on. For example, if the Week_income table has two data and its week is "Monday", one of the income is 1000 and the other income is 500, then the sum is used here, and the value of the column "Monday" After row to column is of course 1500. After for [week] in ([Monday],[Tuesday] ...) In for [week] means

Tags: function apply arc font var. project Bubuko GPO Body Count SELECT * from Tbiz_projectrelation Target statistics each project has several application records Step1 SELECTProjectID, Relationids= STUFF((SELECT ',' + CONVERT(VARCHAR,[Relationid]) fromTbiz_projectrelation TWHEREProjectID=T1. ProjectID forXML PATH ("')),1,1,"') fromTbiz_projectrelation T1 Step2 SELECTProjectID, Relationids= STUFF((SELECT ',' + CONVERT(VARCHAR,[Relationid]) fromTbiz

This function has a none of deterministic, NO SQL, or READS SQL DATA in its declaration and binaryMySQL settings by default are not allowed to create functionsWorkaround 1:Perform:SET GLOBAL log_bin_trust_function_creators = 1;But it doesn't work when it's restarted.Note: The slave must be set at the time of the master copy, or it will cause master-slave synchron

(Str,s) – returns the position of S in str, no then returns 0 SELECT * FROM stud WHERE LENGTH (NAME) >3;SELECT * FROM stud WHERE LENGTH (LTRIM (NAME)) >3; SELECT SUBSTRING (name,1,2) from stud;SELECT * FROM stud WHERE SUBSTRING (name,1,1) = ' King ';//show students surnamed Wang ※ objects between the database and the Java classlistStud class object (Value object, domain object)---table record (one row in the table)Stud attributes (member variables) in a Class object-----table fieldThe next day,

Tags: group_concat find_in_set SQL Server emulation MySQLA value in a field that is separated by a commaFind_in_set (' A ', ' a,b,c,d ') with CHARINDEX (', ' + ' B ' + ', ', ', ' + ' a,b,c ' + ', ') > 0 substitutionIi. examples of group_concat that mimic MySQLselectid, val= (select[value]+ ', ' FROMtbASb whereb.id=a.id forxmlpath (') ) Fromtbas a Groupbyid selectid, [val]=stuff ( (select ', ' +[value] fromtbasb whereb.id=a.id forxmlpath (

Label:I. Connecting queries within a connection Select a. field, B. field from table 1 A, table 2 B where a. field =b. Fields and ... Two. DML Data Manipulation language 1. Add insert INTO values 1-insert into table name values (a list of values corresponding to all field one by one) 2-insert into table name (field list) values (a list that corresponds to the field list one by one) 3-insert into table name select corresponding field list from source table name where condition 4-insert into tabl

SQL median function instance and SQL median instance Add a table or total_sales Table: SQL statement: Copy codeThe Code is as follows: SELECT * from(SELECT a1.Name, a1.Sales, COUNT (a2.sales) Sales_RankFROM Total_Sales a1, Total_Sales a2WHERE a1.Sales Group by a1.Name, a1.SalesOrder by a1.Sales DESC, a1.Name DESC

-- SQL 2000 function RecursionAlgorithmIf object_id ('f _ test') is not null drop function f_testGoCreate Function f_test (@ dt int)Returns intAsBeginDeclare @ I intSelect @ I = @ DT + isnull (DBO. f_test (number), 0) from Master .. spt_values where type = 'p' and number = @ dt-1Return @ IEndGoSelect DBO. f_test (10)

Today, I used the attach function of SQL Server 2008 to attach a database. A problem occurred. The error message is: Unable to open physical file "D: \ Documents ents \ dalt \ XXXX. MDF" Operating System Error 5: "5 (error not found)" (Microsoft SQL Server: Error 5120 )". I remember that there was no problem with the previous operations. I don't know why, so I ca

Tags: system val SQL numeric GPO conversion nvarchar diff lisCONVERT (varchar (10), field name, conversion format)Convert is a date conversion function, which is typically used when the time type (Datetime,smalldatetime) and the string type (Nchar,nvarchar,char,varchar) are converted to each other. The 3 parameters of the function: The 1th argument is the convert

GETDATE (): Gets the current date timeDATEADD (datepart, number, date), calculates the date after the increase. The parameter date is the day to be calculated, the parameter number is increment, the parameter datepart is the unit of measurement, and the optional value is shown in remarks. DATEADD (day, 3,date) is the date 3 days after the date of the calculation, and DATEADD (month, -8,date) is the date 8 months before the date of the calculation.(1000$ of employees with more than one year of em

Select Name,salespersonid from Sales.Store where Name between ' G ' and ' J ' and SalesPersonID > 283 ORDER by Sal Espersonid,name DESC/ASCThe use of the between of this statement is still very innovative, this between is the beginning of G and J between the beginning, do not know what the Chinese characters will be?Group BY, often with the min max sum avg, with the as name alias. The group by Mate aggregation function is commonly used.When Count (col

The time function in SQL is very useful, especially when we are doing initial assignment, complex query, it is particularly convenient. 1. Get System Current Time Select GETDATE () 2, Datename (datepart, date) returns a string representing the specified date part of the specified date. --Today is 2009-2-24--Tuesday SELECT datename (year, GETDATE ()) as ' year Name '--------back: 2009 SELECT datename (