Cast, convert, datediff, and dateadd functions in SQL Server

Source: Internet
Author: User

1. Cast

Cast (data as data_type)

 

Example: declare @ data int

Set @ DATA = 5

Select cast (@ data as char (2) + 'abc'

 

-- Result: 5 ABC

 

2. Convert

Convert (data_type, expresses (, style) -- style is the style of the specified date

  • Select convert (varchar, getdate (), 120)
    -- 15:10:02
  • Select Replace (replace (convert (varchar, getdate (), 120 ),'-',''),'',''),':','')
    -- 20090315151201
  • Select convert (varchar (12), getdate (), 111)
    -- 2009/03/15
  • Select convert (varchar (12), getdate (), 112)
    -- 20090315
  • Select convert (varchar (12), getdate (), 102)
    -- 2009.03.15
  • Select convert (varchar (12), getdate (), 108)
    -- 15:13:26
  • Other date format conversion methods that are not commonly used:
  • Select convert (varchar (12), getdate (), 101)
    -- 03/15/2009
  • Select convert (varchar (12), getdate (), 103)
    -- 15/03/2009
  • Select convert (varchar (12), getdate (), 104)
    -- 15.03.2009
  • Select convert (varchar (12), getdate (), 105)
    -- 15-03-2009
  • Select convert (varchar (12), getdate (), 106)
    -- 15 03 2009
  • Select convert (varchar (12), getdate (), 107)
    -- 15,200 9
  • Select convert (varchar (12), getdate (), 109)
    -- 03 15 2009
  • Select convert (varchar (12), getdate (), 110)
    -- 03-15-2009
  • Select convert (varchar (11), getdate (), 113)
    -- 15 03 2009
  • Select convert (varchar (12), getdate (), 114)
    -- 15: 15: 55: 717

3. datediff

Datediff (datepart, startdate, enddate)

Parameter: datepart list

 

Date Abbreviations
Year YY, yyyy
Quarter QQ, Q
Month Mm, m
Dayofyear Dy, y
Day DD, d
Week Wk, WW
Hour HH
Minute Mi, n
Second SS, S
Millisecond MS

 

 

4. dateadd (datepart, number, date)

View the first day of the week set by the system -- select @ datefirst

 

Examples of combining datediff and dateadd:

-- Query the first day of the current month

Select dateadd (mm, datediff (mm, 0, getdate (), 0)

 

-- Query Monday of the week

Select dateadd (wk, datediff (wk, 0, getdate (), 0)

 

-- Query the first day of a year

Select dateadd (YY, datediff (YY, 0, getdate (), 0)

 

-- Query the first day of a quarter

Select dateadd (QQ, datediff (QQ, 0, getdate (), 0)

 

-- Query the last day of the last month (equivalent to the first day of the month minus one day)

Select dateadd (day,-1, dateadd (mm, datediff (mm, 0, getdate (), 0 ))

 

-- Query the last day of last year

Select dateadd (day,-1, dateadd (YY, datediff (YY, 0, getdate (), 0 ))

 

-- Query the last day of the month
Select dateadd (day,-1, dateadd (mm, datediff (mm, 0, getdate () + 1, 0 ))

 

-- Query the last day of the year

Select dateadd (day,-1, dateadd (YY, datediff (YY, 0, getdate () + 1, 0 ))

 

 

-- Query the first Monday of the month

Another function is required here.

Datepart (datepart, date)-- Return the integer part of the specified type of the specified date.

Select dateadd (wk, datediff (wk, 0, dateadd (DD, 6-datepart (day, getdate (), getdate (), 0)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.