sql server query statement, date and time format

2010-09-26  来源:本站原创  分类:Database  人气:136 

Sql Server in a very powerful date formatting function
Select CONVERT (varchar (100), GETDATE (), 0): 05 16 2006 10:57 AM
Select CONVERT (varchar (100), GETDATE (), 1): 05/16/06
Select CONVERT (varchar (100), GETDATE (), 2): 06.05.16
Select CONVERT (varchar (100), GETDATE (), 3): 16/05/06
Select CONVERT (varchar (100), GETDATE (), 4): 16.05.06
Select CONVERT (varchar (100), GETDATE (), 5): 16-05-06
Select CONVERT (varchar (100), GETDATE (), 6): 16 05 06
Select CONVERT (varchar (100), GETDATE (), 7): 05 16, 06
Select CONVERT (varchar (100), GETDATE (), 8): 10:57:46
Select CONVERT (varchar (100), GETDATE (), 9): 05 16 2006 10:57:46:827 AM
Select CONVERT (varchar (100), GETDATE (), 10): 05-16-06
Select CONVERT (varchar (100), GETDATE (), 11): 06/05/16
Select CONVERT (varchar (100), GETDATE (), 12): 060516
Select CONVERT (varchar (100), GETDATE (), 13): 16 05 2006 10:57:46:937
Select CONVERT (varchar (100), GETDATE (), 14): 10:57:46:967
Select CONVERT (varchar (100), GETDATE (), 20): 2006-05-16 10:57:47
Select CONVERT (varchar (100), GETDATE (), 21): 2006-05-16 10:57:47.157
Select CONVERT (varchar (100), GETDATE (), 22): 05/16/06 10:57:47 AM
Select CONVERT (varchar (100), GETDATE (), 23): 2006-05-16
Select CONVERT (varchar (100), GETDATE (), 24): 10:57:47
Select CONVERT (varchar (100), GETDATE (), 25): 2006-05-16 10:57:47.250
Select CONVERT (varchar (100), GETDATE (), 100): 05 16 2006 10:57 AM
Select CONVERT (varchar (100), GETDATE (), 101): 05/16/2006
Select CONVERT (varchar (100), GETDATE (), 102): 2006.05.16
Select CONVERT (varchar (100), GETDATE (), 103): 16/05/2006
Select CONVERT (varchar (100), GETDATE (), 104): 16.05.2006
Select CONVERT (varchar (100), GETDATE (), 105): 16-05-2006
Select CONVERT (varchar (100), GETDATE (), 106): 16 05 2006
Select CONVERT (varchar (100), GETDATE (), 107): 05 16, 2006
Select CONVERT (varchar (100), GETDATE (), 108): 10:57:49
Select CONVERT (varchar (100), GETDATE (), 109): 05 16 2006 10:57:49:437 AM
Select CONVERT (varchar (100), GETDATE (), 110): 05-16-2006
Select CONVERT (varchar (100), GETDATE (), 111): 2006/05/16
Select CONVERT (varchar (100), GETDATE (), 112): 20060516
Select CONVERT (varchar (100), GETDATE (), 113): 16 05 2006 10:57:49:513
Select CONVERT (varchar (100), GETDATE (), 114): 10:57:49:547
Select CONVERT (varchar (100), GETDATE (), 120): 2006-05-16 10:57:49
Select CONVERT (varchar (100), GETDATE (), 121): 2006-05-16 10:57:49.700
Select CONVERT (varchar (100), GETDATE (), 126): 2006-05-16T10: 57:49.827
Select CONVERT (varchar (100), GETDATE (), 130): 18???? ?????? 1427 10:57:49:907 AM
Select CONVERT (varchar (100), GETDATE (), 131): 18/04/1427 10:57:49:920 AM

Common:
Select CONVERT (varchar (100), GETDATE (), 8): 10:57:46
Select CONVERT (varchar (100), GETDATE (), 24): 10:57:47
Select CONVERT (varchar (100), GETDATE (), 108): 10:57:49
Select CONVERT (varchar (100), GETDATE (), 12): 060516
Select CONVERT (varchar (100), GETDATE (), 23): 2006-05-16

相关文章
  • sql server query statement, date and time format 2010-09-26

    Sql Server in a very powerful date formatting function Select CONVERT (varchar (100), GETDATE (), 0): 05 16 2006 10:57 AM Select CONVERT (varchar (100), GETDATE (), 1): 05/16/06 Select CONVERT (varchar (100), GETDATE (), 2): 06.05.16 Select CONVERT (

  • In Sql Server query statement can not be expressed with variable table name 2010-09-02

    Description: This is the query in the Sql Server can use the variable in that table name - MS-SQL Server / base class details page, introduced and mssql, in Sql Server query statement can not be expressed with variable table name - MS- SQL Server / b

  • SQL SERVER convert the date format function application. 2010-05-31

    SQL SERVER convert the date format function application. Sql Server in a very powerful date formatting function Select CONVERT (varchar (100), GETDATE (), 0): 05 16 2006 10:57 AM Select CONVERT (varchar (100), GETDATE (), 1): 05/16/06 Select CONVERT

  • MS SQL Server Query Optimization 2010-06-09

    MS SQL Server Query Optimization Study: xmllover 2007-11-29 Query slow for many reasons, as several common 1, no index or index is not used (this is the most common problems slow query is programming defects) 2, I / O throughput is small, the formati

  • Console version of the SQL Server Query Analyzer 2010-06-19

    SQL Server Query Analyzer on the use of its internal operating mechanism can have a preliminary understanding; combined system on the Enterprise Manager that comes with the analysis of some documents, the user's operating results are reflected in one

  • Sql Server string. Collection date functions 2010-08-31

    /**//********************************************* ****************************** DATEPART Return date specified on behalf of the specified date part of the integer. Grammar DATEPART (datepart, date) Parameter datepart Is part of the specified date s

  • oracle and sql server query SQL statement page 2010-03-25

    1.Oracle paging sql statement SELECT * FROM (SELECT A.*, ROWNUM RN FROM ( select sno,sname,sage,ssex from tb_student where 1=1 ) A WHERE ROWNUM <'24') WHERE RN >='0' 2.Sql Server paging sql statement SELECT sno,sname,sage,ssex FROM ( SELECT ROW_NUMB

  • SQL Server page statement 2011-09-21

    Turn: a lot of information about SQL page, and some use of stored procedures, and some use the cursor. I do not like to use a cursor, I think it cost and low efficiency; using stored procedures is a good choice, because the stored procedure is precom

  • Sql Server in the date and time functions 2010-03-18

    1. The current system date, time, select getdate () 2. Dateadd to add the specified date on the basis of a period of time, to return the new datetime value: For example: add two days to date select dateadd (day, 2, '2004-10-15 ') - return :2004-10-7

  • sql server query to a database of all the task execution state 2010-12-06

    xp_sqlagent_enum_jobs parameters: xp_sqlagent_enum_jobs <is sysadmin (0 or 1)>, <job owner name> [, <job id>] Notes: @ Is sysadmin = 1 @ Job owner name can easily give a string value @ Job id is optional. For example, you can check all t

  • sql server query based on date field 2010-11-08

    select * from tableName where datediff (day, dateField, getdate ()) = 4 dateField tableName the table is the date of a field in the name of the sql statement to query the records from the four-day today, as today if it was 11-08, the query is dateFie

  • SQL Server paging statement 2010-02-23

    Description: A query returns the current page of data. pagesize: number of records per page cureentpage: Current Page select * from (select TOP pagesize * FROM (SELECT TOP pagesize * cureentpage * from user_table ORDER BY id ASC) as aSysTable ORDER B

  • sql server string and date of the transfer function 2010-07-09

    String converted to date: cast ('20100514 'as datetime) Date into a string: CONVERT (nvarchar (30), GETDATE (), 126) Date formatting rules are as follows: 1101 U.S. mm / dd / yyyy 2 102 ANSI yy.mm.dd 3103 UK / France dd / mm / yy 4104 Germany dd.mm.y

  • sql server query n to m pieces of data paging imitation mysql limit 2010-03-30

    1 select top m-n +1 * from table where ID not in (select top n-1 ID from table) 2 Select * From (Select Top M-N +1 * From (Select Top M * From Tablec order by ID asc) T Order By ID Desc) TT Order By ID Or as follows: Syntax: SELECT * FROM ( SELECT TO

  • SQL Server query table field information 2010-10-17

    SELECT SO.name The table name , SC.name Table column names , SC.colid Index , ST.name Type FROM sysobjects SO, -- Object table syscolumns SC, -- Column name table systypes ST -- Data type table WHERE SO.id = SC.id AND SO.xtype = 'U' -- Type u table ,

  • sql server query and method to delete duplicate records 2010-12-22

    First, according to repeat a single field 1, the lookup table in the extra duplicate records, duplicate records are based on a single field (peopleId) to determine select * from people where peopleId in (select peopleId from people group by peopleId

  • sql server query the database that contains a text of all stored procedures. Views and Functions SQL 2010-12-28

    Method One: select * from sysobjects o, syscomments s where o.id = s.id and text like '%yyao%' and o.xtype = 'P' Replace the yyao own text to find Method Two: select routine_name,routine_definition,routine_type from information_schema.routines where

  • sql server query, and delete duplicate records method 2010-12-22

    First, according to the duplication of a single field 1, the lookup table in excess of duplicate records, duplicate records are based on a single field (peopleId) to determine select * from people where peopleId in (select peopleId from people group

  • sql server query the database that contain the text of a stored procedure and functions of the SQL view 2010-12-28

    Method One: select * from sysobjects o, syscomments s where o.id = s.id and text like '%yyao%' and o.xtype = 'P' Will yyao replace their text to find Method Two: select routine_name,routine_definition,routine_type from information_schema.routines whe

  • Sql Server, a powerful date formatting function 2011-03-17

    Select CONVERT (varchar (100), GETDATE (), 0): 05 16 2006 10:57 AM Select CONVERT (varchar (100), GETDATE (), 1): 05/16/06 Select CONVERT (varchar (100), GETDATE (), 2): 06.05.16 Select CONVERT (varchar (100), GETDATE (), 3): 16/05/06 Select CONVERT