SQL statements about date time, type conversion things

A

1, select Update_date, convert (varchar (), update_date,111) JJ, CONVERT (varchar (), update_date,8) AA from Report_ Month//update_date is a datetime type and results are shown as:

Update_date JJ AA

2007-01-06 16:14:50.437 2007/01/06 16:14:50

Select DATEPART (mm,sign_date) Montha,datepart (dd,sign_date) daya,sign_date from Report_day

Montha Daya Sign_date

5 1 2009-05-01 00:00:00.000

SELECT datename (month, GETDATE ()) as ' month Name '

2. Query by Date segment: SELECT * from Report_day where sign_date between ' 2009-5-1 ' and ' 2009-5-7 ' (v)

SELECT * from Report_day where sign_date between ' 2009-4 ' and ' 2009-5 ' (x)

3.----Date function----
Day ()--the function returns the date value in Date_expression
Month ()--function returns the month value in Date_expression
Year ()---function returns the value of the years in Date_expression
DATEADD (<datepart>,<number>,<date>)
--The function returns the new date that is generated by the specified date, plus the specified extra date interval number
DATEDIFF (<datepart>,<number>,<date>)
--function returns the difference in DatePart of two specified dates
Datename (<datepart>, <date>)--function returns the specified part of a date as a string
DATEPART (<datepart>, <date>)--function returns the specified part of a date as an integer value
GETDATE ()--function returns the current date and time of the system in the default format of datetime

----A data type conversion function----
The CAST () function syntax is as follows
CAST (<expression> as <data_ type>[length])
The CONVERT () function syntax is as follows
CONVERT (<data_ type>[length], <expression> [, Style])

Select CAST (shift_id as int) +1 AA from Report_day

Select SUM (CONVERT (real,work_days)), week_days AA from Report_day GROUP by Week_days

Adoquery2. Sql.add (' Select emp_id, round (sum (CAST (ot_hrs as Real), 2) overtime at ordinary times, round (sum (CAST (work_hrs as Real), 2) working hours from Report_ Day GROUP by emp_id ');

For example: Select Id,a.name,a.age,
convert (int, sum (case wage item when ' base pay ' then payroll amount end)) Base salary,
Cast (sum (case wage project when ' subsidy ' then payroll amount end) as int) as subsidy,
Convert (int,sum (case wage project when ' allowance ' then payroll Amount end)) as allowance,
Convert (int,sum (wage amount)) as Total
From a, where id=emp_id group by id,a.name,a.age

4. Yesterday's thought of data by date, and later on their own computer to see the previous information, found a solution, is indeed very practical. Plainly, it is
Just want to get a part of the date or time, not the full date and time. And then in statistics.

Databases and tables used (MS SQL Server database)
Use master
Go

if exists (SELECT * from sysdatabases where name= ' spending ')
Drop DATABASE Spending

Create DATABASE spending
On
(
Name= ' Spending_data ',
Filename= ' D:\Spending_data.mdf ',
size=3 MB,
filegrowth=10%
)
Log on
(
Name= ' Spending_log ',
Filename= ' D:\Spending_log.ldf ',
size=3 MB,
filegrowth=10%
)
Go


Use spending
Go

if exists (select * from sysobjects where name= ' consumption ')
drop table consumption
Go
CREATE TABLE consumption
(
CID int PRIMARY key identity (+),
CDate datetime NOT NULL,
Cmoney money is not null
)
Go


Insert into consumption values (' 20090624 ', 23)
Insert into consumption values (' 20090625 ', 56)
Insert into consumption values (' 20090625 ', 42)
Insert into consumption values (' 20090626 ', 45)
Insert into consumption values (' 20090627 ', 12)
Insert into consumption values (' 20090628 ', 32)
Insert into consumption values (' 20090629 ', 36)
Insert into consumption values (' 20090701 ', 16)

SELECT * FROM consumption

For example, I want to count the daily data and (two records at 25th) Use the following statement to count only the data for each day of June and (if the year is more, plus the restrictions)

Select DATEPART (dd,cdate) as ' number ', sum (Cmoney) from consumption where DATEPART (mm,cdate) =6 GROUP by DATEPART (Dd,cdate)
Show results
24 23.00
25 98.00
26 45.00
、、、

Statistics for one week are as follows

Select DATEPART (wk,cdate) as ' Week ', sum (Cmoney) from consumption Group by DATEPART (Wk,cdate)
Show results
26 178.00
27 84.00

Statistics of the month are as follows

Select DATEPART (mm,cdate) as ' month ', sum (Cmoney) from consumption where DATEPART (yy,cdate) =2009 GROUP by DATEPART (mm,cdate)

Show results
6 246.00
7 16.00

The above is the "DATEPART ()" function. The parameter of the function datepart () is two variables. The first variable specifies which part of the date to extract, and the second variable is the actual data.

The parts of the date and their abbreviations
Date Part shorthand value
Year YY 1753--9999
Quarter QQ 1--4
month mm 1--12
Day of the year dy 1--366
Day DD 1--31
Week wk 1--53
Weekday DW 1--7 (Sunday--saturday)
Hour HH 0--23
Minute Mi 0--59
Second SS 0--59
Milisecond Ms 0--999




And then there's our database. After a field is designed as a datetime type, the number of milliseconds is automatically displayed after inserting the data.
For example, I inserted the above is 20090624, but in the detection to become the 2009-06-24 00:00:00.000, so on the page
Display must be problematic, this can be used with the CONVERT () function

CONVERT (Data_type,expression,[style])

This style is typically in the time type (Datetime,smalldatetime) and the string type (Nchar,nvarchar,char,varchar)
It is only used when converting to each other.

Statements and Query results:
SELECT CONVERT (varchar), CDate, 0): 21:57pm
SELECT CONVERT (varchar), CDate, 1): 06/24/09
SELECT CONVERT (varchar), CDate, 2): 09.06.24
SELECT CONVERT (varchar), CDate, 3): 24/06/09
SELECT CONVERT (varchar), CDate, 4): 24.06.09
SELECT CONVERT (varchar), CDate, 5): 24-06-09
SELECT CONVERT (varchar), CDate, 6): 24 06 09
SELECT CONVERT (varchar), CDate, 7): 06 24, 09
SELECT CONVERT (varchar), CDate,: 10:57:46
SELECT CONVERT (varchar), CDate, 9): 21:57:46:827am
SELECT CONVERT (varchar), CDate, 10): 06-24-09
SELECT CONVERT (varchar), CDate, 11): 09/06/24
SELECT CONVERT (varchar), CDate, 12): 090624
SELECT CONVERT (varchar), CDate, 13): 24 06 2009 21:57:46:937
SELECT CONVERT (varchar), CDate, 14): 10:57:46:967
SELECT CONVERT (varchar), CDate, 20): 2009-06-24 21:57:47
SELECT CONVERT (varchar), CDate, 21): 2009-06-24 21:57:47.157
SELECT CONVERT (varchar), CDate: 06/24/09 21:57:47 PM
SELECT CONVERT (varchar), CDate, 23:2009-06-24
SELECT CONVERT (varchar), CDate, 24): 10:57:47
SELECT CONVERT (varchar), CDate, 25): 2009-06-24 21:57:47.250
SELECT CONVERT (varchar), CDate, (+): 21:57pm
SELECT CONVERT (varchar), CDate, 101:06/24/2009
SELECT CONVERT (varchar), CDate, 102:2009.06.24
SELECT CONVERT (varchar), CDate, 103:24/06/2009
SELECT CONVERT (varchar), CDate, 104): 24.06.2009
SELECT CONVERT (varchar), CDate, 105:24-06-2009
SELECT CONVERT (varchar), CDate, 106): 24 06 2009
SELECT CONVERT (varchar), CDate, 107): 06 24, 2009
SELECT CONVERT (varchar), CDate, 108): 10:57:49
SELECT CONVERT (varchar), CDate, 109): 21:57:49:437pm
SELECT CONVERT (varchar), CDate, 110:06-24-2009
SELECT CONVERT (varchar), CDate, 111:2009/06/24
SELECT CONVERT (varchar), CDate, 112): 20090624
SELECT CONVERT (varchar), CDate, 113): 24 06 2009 21:57:49:513
SELECT CONVERT (varchar), CDate, 114): 10:57:49:547
SELECT CONVERT (varchar), CDate, 120): 2009-06-24 21:57:49

4,

1. Show the first day of the month

SELECT DATEADD (Mm,datediff (Mm,0,getdate ()), 0)
Select CONVERT (Datetime,convert (varchar (8), GETDATE (),
120) + ' 01 ', 120)

2. Show last day of the month

Select DATEADD (Day,-1,convert (Datetime,convert
(varchar (8), DATEADD (Month,1,getdate ()), 120) + ' 01 ', 120))
SELECT DateAdd (Ms,-3,dateadd (Mm,datediff (M,0,getdate ()) +1,0)


3. Last day of last month

SELECT DateAdd (Ms,-3,dateadd (Mm,datediff (Mm,0,getdate ()), 0))

4. The first Monday of this month I

Select DATEADD (Wk,datediff (wk,0, DATEADD (Dd,6-datepart (
Day,getdate ()), GETDATE ()), 0)


5. First day of the year

SELECT DATEADD (Yy,datediff (Yy,0,getdate ()), 0)

6. Last day of the year

SELECT DateAdd (Ms,-3,dateadd (Yy,datediff (Yy,0,getdate ()) +1,0)

7. Last day of last year

SELECT DateAdd (Ms,-3,dateadd (Yy,datediff (Yy,0,getdate ()), 0))


8. The first day of the quarter

SELECT DATEADD (Qq,datediff (Qq,0,getdate ()), 0)


9. This week's Monday

SELECT DATEADD (Wk,datediff (Wk,0,getdate ()), 0)


10. Check this month's records

SELECT * from TableName where DATEPART (mm, thedate)
=datepart (mm, GETDATE ()) and DATEPART (yy, thedate)
= DATEPART (yy, GETDATE ())

11. Query this week's records

SELECT * from TableName where DATEPART (wk, thedate) = DATEPART
(wk, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GETDATE ())

12 Check this season's records note: GETDATE () is a function that obtains system time.

SELECT * from TableName where DATEPART (QQ, thedate) = DATEPART
(QQ, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GETDATE ())

13. Get the total number of days in the month:

Select DATEDIFF (Dd,getdate (), DATEADD
(mm, 1, GETDATE ()))

Select DateDiff (Day,
DATEADD (mm, DateDiff (mm, ", GETDATE ()),"),
DATEADD (mm, DateDiff (mm, ", GETDATE ()), ' 1900-02-01 ')

14. Get the current day of the week

Datename (Weekday, GETDATE ())