SQL Server to get the current time format conversion
Different DATETIME formats.
DBAs are always looking for different ways to return the date. Strip off the time, present the actual day or month name, include 'AM' or 'PM', use different separators... and on and on. I've listed a few of my date formatting examples here. Take a look, and let me know if you need something more. Also, please remember that formatting or presentation of the data really should be done on the front end. This stuff may be pretty, but it's not actually free. Please keep in mind, there is overhead to formatting the date data like this.
SELECT CONVERT(VARCHAR(10),GETDATE(),1) [MM/DD/YY]
SELECT CONVERT(VARCHAR(10),GETDATE(),4) [MM.DD.YY]
SELECT CONVERT(VARCHAR(12),GETDATE(),111) [YYYY/MM/DD]
SELECT CONVERT(VARCHAR(12),GETDATE(),101) [MM/DD/YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),102) [YYYY.MM.DD]
SELECT CONVERT(VARCHAR(12),GETDATE(),103) [DD/MM/YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),104) [DD.MM.YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),105) [DD-MM-YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),113) [DD Mon YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),108) [hh:mm:ss]
SELECT CONVERT(VARCHAR(12),GETDATE(),110) [MM-DD-YYYY]
SELECT SUBSTRING(CONVERT(VARCHAR(11),DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),-1),113),4,8) [Mon YYYY]
SELECT LEFT(GETDATE(),11) [Mon D, YYYY]
SELECT CONVERT(CHAR(8),GETDATE(),112) [YYYYMMDD]
SELECT LEFT(CONVERT(CHAR(10),DATEADD(dd,-0,GETDATE()),101),5) [MM/DD]
SELECT CONVERT(DATETIME,CONVERT(CHAR(10),GETDATE(),120),120) [YYYY-MM-DD hh:mm:ss:ms ]
SELECT CONVERT(VARCHAR(10),GETDATE(),101)+ ''+SUBSTRING(CONVERT(VARCHAR(20),GETDATE(),100),13,7) [MM/DD/YYYY hh:mmPM]
SELECT DATENAME(MONTH,GETDATE()) AS [Month]
SELECT DATENAME(m, DATEADD ( m , -1, GETDATE() )) [Last Month]
SELECT LEFT(getdate()-0, 11) [Today], LEFT(DATEADD(dy, 1, GETDATE()),11) [Tomorrow]
SELECT CONVERT(CHAR(5),GETDATE(),8) [hh:mm]
SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),100),13,9) [hh:mmPM]
SELECT CONVERT(VARCHAR(100),GETDATE(),114) [hh:mm:ss:ms ]
SQL Server with SQL statement How to get the current system time is to use GETDATE ();
Sql getDate ()
Tuesday, January 8, 2008, 14:59
A very powerful date formatting functions in SQL Server
Select CONVERT (varchar (100), GETDATE (), 0): 05 16 2008 10:57 AM
Select CONVERT (varchar (100), GETDATE (), 1): 05/16/08
Select CONVERT (varchar (100), GETDATE (), 2): 08.05.16
Select CONVERT (varchar (100), GETDATE (), 3): 16/05/08
Select CONVERT (varchar (100), GETDATE (), 4): 16.05.08
Select CONVERT (varchar (100), GETDATE (), 5): 16-05-08
Select CONVERT (varchar (100), GETDATE (), 6): 16 05 08
Select CONVERT (varchar (100), GETDATE (), 7): 05 16, 08
Select CONVERT (varchar (100), GETDATE (), 8): 10:57:46
SELECT CONVERT (varchar (100), GETDATE (), 9): 05 16 2008 10:57:46:827 AM
Select CONVERT (varchar (100), GETDATE (), 10): 05-16-08
Select CONVERT (varchar (100), GETDATE (), 11): 16/05/08
Select CONVERT (varchar (100), GETDATE (), 12): 080516
Select CONVERT (varchar (100), GETDATE (), 13): 16 05 2008 10:57:46:937
Select CONVERT (varchar (100), GETDATE (), 14): 10:57:46:967
Select CONVERT (varchar (100), GETDATE (), 20): 2008-05-16 10:57:47
Select CONVERT (varchar (100), GETDATE (), 21): 2008-05-16 10:57:47.157
Select CONVERT (varchar (100), GETDATE (), 22): 05/16/08 10:57:47 AM
Select CONVERT (varchar (100), GETDATE (), 23): 2008-05-16
Select CONVERT (varchar (100), GETDATE (), 24): 10:57:47
Select CONVERT (varchar (100), GETDATE (), 25): 2008-05-16 10:57:47.250
Select CONVERT (varchar (100), GETDATE (), 100): 05 16 2008 10:57 AM
Select CONVERT (varchar (100), GETDATE (), 101): 05/16/2008
Select CONVERT (varchar (100), GETDATE (), 102): 2008.05.16
Select CONVERT (varchar (100), GETDATE (), 103): 16/05/2008
Select CONVERT (varchar (100), GETDATE (), 104): 16.05.2008
Select CONVERT (varchar (100), GETDATE (), 105): 16-05-2008
Select CONVERT (varchar (100), GETDATE (), 106): 16 05 2008
Select CONVERT (varchar (100), GETDATE (), 107): 05 16, 2008
Select CONVERT (varchar (100), GETDATE (), 108): 10:57:49
Select CONVERT (varchar (100), GETDATE (), 109): 05 16 2008 10:57:49:437 AM
Select CONVERT (varchar (100), GETDATE (), 110): 05-16-2008
Select CONVERT (varchar (100), GETDATE (), 111): 2008/05/16
Select CONVERT (varchar (100), GETDATE (), 112): 20080516
Select CONVERT (varchar (100), GETDATE (), 113): 16 05 2008 10:57:49:513
Select CONVERT (varchar (100), GETDATE (), 114): 10:57:49:547
Select CONVERT (varchar (100), GETDATE (), 120): 2008-05-16 10:57:49
Select CONVERT (varchar (100), GETDATE (), 121): 2008-05-16 10:57:49.700
Select CONVERT (varchar (100), GETDATE (), 126): 2008-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
Type value standard output
0 Default mon dd yyyy hh: miAM
1 USA mm / dd / yy
2 ANSI yy.mm.dd
3 British / French dd / mm / yy
4 German dd.mm.yy
5 Italian dd-mm-yy
6 - dd mon yy
7 - mon dd, yy
8 - hh: mi: ss
9 Default + millisecondsR11; mon dd yyyy hh: mi: ss: mmmAM (or)
10 USA mm-dd-yy
11 JAPAN yy / mm / dd
12 ISO yymmdd
13 Europe Default + millisecondsR11; dd mon yyyy hh: mi: ss: mmm (24h)
14 - hh: mi: ss: mmm (24h)
In front of the type of value, such as 101 mm / dd / yyyy
-------------------------------------------------- -------------------------------
Sometimes some of the fields in the database because of the special consideration of the relationship between these fields is a char or nchar format to store date
If you want these fields to transit into a consistent date format (such as yyyy / mm / dd)
1988/1/1 1988/01/01 words turn into trying to refer to the following syntax ...
SELECT CONVERT (varchar (10), CONVERT (datetime employee_birthday, 111), 111)
FROM employee
Other format conversion following example:
--YYYY/MM/DD
Select Convert (varchar (10), getdate (), 111)
- YYYYMMDD
Select Convert (varchar (10), getdate (), 112)
- HH: MM: SS
Select Convert (varchar (8), getdate (), 108)
- HH: MM: SS: mmm
Select Convert (varchar (12), getdate (), 114)
-------------------------------------------------- -------------------------------
How to use sql language database current date format to "yyyy-mm-dd?
select convert (char (10), getdate (), 120)
-------------------------------------------------- -------------------------------
- Characters converted to a date, Style use
- 1. Style = 101, which means that the date string: mm / dd / yyyy format
SELECT CONVERT (datetime, '11 / 1/2003 ', 101)
- Results :2003-11-01 00:00:00.000
- 2. Style = 101, which means that the date string: dd / mm / yyyy format
SELECT CONVERT (datetime, '11 / 1/2003 ', 103)
- Results :2003-01-11 00:00:00.000
DECLARE @ dt datetime
SET @ dt = '2003-1-11 '
- 1. Style = 101, which means that to convert the date: mm / dd / yyyy format
SELECT CONVERT (varchar, @ dt, 101)
- Results: 01/11/2003
- 2. Style = 103, which means that to convert the date: dd / mm / yyyy format
SELECT CONVERT (varchar, @ dt, 103)
- Results: 11/01/2003
SELECT CONVERT (varchar, '2003-1-11 ', 101)
- Results :2003-1-11
- 1.
- Example In the following example, the first CONVERT conversion is not specified style, the result of the conversion is by SET DataFormat, impact, second CONVERT convert the style is specified, the conversion results were affected by the impact of the style.
- Set the input on the order of day / month / year
SET DATEFORMAT DMY.
- You do not specify the the Style parameters of CONVERT conversion will be SET DATEFORMAT
SELECT CONVERT (datetime, '2 -1-2005 ')
- Results: 2005-01-02 00:00:00.000
- The specified parameters CONVERT Style conversion SET DATEFORMAT does not affect
SELECT CONVERT (datetime, '2 -1-2005 ', 101)
- Results: 2005-02-01 00:00:00.000
GO
- 2.
- Example In the following code, the same SET DATEFORMAT settings century part of the input date do not enter the date of the Century part, explain different date results.
DECLARE @ dt datetime
- Set the SET DATEFORMAT: Month Day Year
SET DATEFORMAT MDY.
- Enter the date specified in the century part
SET @ dt = '01-2002-03 '
SELECT @ dt
- Results: 2002-01-03 00:00:00.000
- The date entered is not specified in the part of the century
SET @ dt = '01-02-03 '
SELECT @ dt
- Results: 2003-01-02 00:00:00.000
GO
- 3.
- Example, in the following code does not contain the date of the date separator character, the interpretation of the result is the same in different SET DATEFORMAT settings.
DECLARE @ dt datetime
- Set the SET DATEFORMAT: Month Day Year
SET DATEFORMAT MDY.
SET @ dt = '010203 '
SELECT @ dt
- Results: 2001-02-03 00:00:00.000
- Set the SET DATEFORMAT: Sun and Moon
SET DATEFORMAT DMY.
SET @ dt = '010203 '
SELECT @ dt
- Results: 2001-02-03 00:00:00.000
- The date entered date separator
SET @ dt = '01-02-03 '
SELECT @ dt
- Results: 2003-02-01 00:00:00.000
- The following example demonstrates the different results in different locales (SET LANGUAGE), DATENAME CONVERT function.
USE master
- Set the session language: English
SET LANGUAGE N'English the '
SELECT
DATENAME (Month, GETDATE ()) AS [Month],
DATENAME (Weekday, GETDATE ()) AS [Weekday]
CONVERT (varchar, GETDATE (), 109) AS [CONVERT]
- Set the session language environment: Simplified Chinese
SET LANGUAGE N 'Simplified Chinese'
SELECT
DATENAME (Month, GETDATE ()) AS [Month],
DATENAME (Weekday, GETDATE ()) AS [Weekday]
CONVERT (varchar, GETDATE (), 109) AS [CONVERT]
--------------------------------------------------------------------------------------------------
Calculating a Different Date
I'm sure we've all had a need to calculate dates. Basically, taking the current date and doing some math on it, in order to derive another date. For example, the first day of the month or week or quarter, the last day of this month or last, or even the last day of this year. In this tip I will just show you a quick way to perform this type of calculation, using DATEADD, DATEPART and DATEDIFF:
DECLARE @today DATETIME
DECLARE @datefirst TINYINT
SET @today = CONVERT(CHAR(8), GETDATE(), 112)
SET @datefirst = @@DATEFIRST
SET DATEFIRST 7
SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @today)) + 1, @today) [1st Day of This Week]
SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @today)) + 7, @today) [Last Day of This Week]
SELECT DATEADD(DAY, -DATEPART(DAY, @today) + 1, @today) [1st Day of This Month]
SELECT DATEADD(qq, DATEDIFF(qq,0,@today), 0) [1st Day of This Qtr]
SELECT DATEADD(DAY, -DATEPART(DAY, @today), DATEADD(MONTH, 1, @today)) [Last Day of This Month]
SELECT DATEADD(MONTH, -1, DATEADD(DAY, - DAY(@Today) + 1, @Today)) [1st Day of Last Month]
SELECT DATEADD(DAY, - DAY(@Today), @Today) [Last Day of Last Month]
SELECT DATEADD(DAY, -DATEPART(DAYOFYEAR, @today) + 1, @today) [1st Day of This Year]
SELECT CAST(CAST(YEAR(@today) AS CHAR(4)) + '1231' AS DATETIME) [Last Day of This Year]
SET DATEFIRST @datefirst
The above is all very dynamic, based on @today. This example will actually show you how to count the number of days, weekdays or even the hours, between two given dates, @StartDate and @EndDate:
DECLARE @StartDate DATETIME = '1/1/2011',
@EndDate DATETIME = GETDATE()
/* Calculate # of Days between two dates */
SELECT DATEDIFF(dd,@StartDate,@EndDate) [Total # Days]
/* Calculate # of Weekdays between same two dates */
SELECT DATEDIFF(d,@StartDate,@EndDate)+1
- (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@StartDate)=1 then 1 else 0 End )
- (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@EndDate)=7 then 1 else 0 End ) [Total # Weekdays]
/* Calculate # of Hours between same two dates */
SELECT DATEDIFF(second,@startdate,@EndDate)/60.0/60.0 [Total # Hours]