SQL Server Date Format Function

SQL Server date format function

In SQL Server, a DATETIME datatype is stored as 2 4-byte integers so as such doesn't have a particular formatting like this.

If you want to return the date in a specific format, you need to CONVERT it to VARCHAR with the appropriate format identifier specified.

If you have a datetime in a VARCHAR and want to store that in a DATETIME field in SQL Server, then you should make sure you pass that value to SQL in a format that will always be safely interpreted. e.g. dd/mm/YYYY format is not safe as depending on settings, it could be treated as mm/dd/yyyy when it goes in. Safe formats are:

yyyyMMdd
yyyy-MM-ddThh:mi:ss.mmm

e.g.

INSERT MyTable (DateField) VALUES ('01/10/2010') -- dd/MM/yyyy not safe
INSERT MyTable (DateField) VALUES ('20101001') -- yyyyMMdd safe

Update:

When you SELECT a DATETIME field (GETDATE(), field, variable....) what you see in SSMS is a formatted value as this is what is useful to you, instead of it showing it's actual internal 8byte representation.

DATEFORMAT FUNCTION IN SQL

The error when creating the view with SET DATEFORMAT dmy is due to a view being an encapsulation of a single query, not multiple commands. If you need multiple commands, then you would have to use a Multistatement Table-Valued Function. But using a TVF is not necessary here.

Use TRY_CONVERT as it will handle both the translation and the "ISDATE" behavior. It will either convert to a proper DATETIME or it will return NULL. In this sense, a non-NULL value equates to ISDATE returning 1 while a NULL value equates to ISDATE returning 0. Since your data is in the format of DD/MM/YYYY, that is the "style" number 103 (full list of Date and Time styles found on the CAST and CONVERT MSDN page).

SELECT TRY_CONVERT(DATETIME, tmp.DateDDMMYYYY, 103) AS [ConvertedDate],
       tmp.ShouldItWork
FROM (
   VALUES('23/05/2014', 'yes'),
         ('05/23/2014', 'no'),
         ('0a/4f/2014', 'no')
 ) tmp(DateDDMMYYYY, ShouldItWork);

Results:

ConvertedDate            ShouldItWork
2014-05-23 00:00:00.000  yes
NULL                     no
NULL                     no

How to format date on MS SQL Server 2008

I had a similar problem in SQL 2012 they have a great function called format which you could pass in a date and the format you require

SELECT FORMAT(GETDATE(), 'dd/mm/yyy');

put there is nothing similar in SQL Server 2008. I created a simple function to do a similar job, it might need tweaking but is not a bad base.

-- =============================================
-- Author:      Luke Mc Redmond
-- =============================================
CREATE FUNCTION [dbo].[FormatDate]
(
-- Add the parameters for the function here
@Date   DATETIME,
@Format NVARCHAR(50)
)
RETURNS NVARCHAR(50)
AS
 BEGIN

   DECLARE @ResultVar nvarchar(50) = UPPER(@Format);


   DECLARE @d nvarchar(20) = CONVERT(nvarchar(20), DATEPART(day, @Date));
   DECLARE @dd nvarchar(20) = CONVERT(nvarchar(20), DATEPART(day, @Date));
   DECLARE @day nvarchar(20) = DATENAME(weekday, @Date);
   DECLARE @ddd nvarchar(20) = DATENAME(weekday, @Date)+' '+CONVERT(nvarchar(5), DATEPART(day, @Date));


   DECLARE @m nvarchar(20) = CONVERT(nvarchar, DATEPART(month, @Date));
   DECLARE @mm nvarchar(20) = CONVERT(nvarchar, DATEPART(month, @Date));
   DECLARE @mmm nvarchar(20) = CONVERT(VARCHAR(3), DATENAME(month, @Date), 100);
   DECLARE @month nvarchar(20) = DATENAME(month, @Date);

   DECLARE @y nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));
   DECLARE @yy nvarchar(20) = RIGHT(CONVERT(nvarchar, DATEPART(year, GETDATE())),2);
   DECLARE @yyy nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));
   DECLARE @yyyy nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));
   DECLARE @year nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));

   SELECT @ResultVar = CASE WHEN CHARINDEX('DAY',@ResultVar) > 0 THEN  REPLACE(@ResultVar collate Latin1_General_CS_AS ,'DAY' collate Latin1_General_CS_AS ,@day)
                        WHEN CHARINDEX('DDD',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'DDD' collate Latin1_General_CS_AS ,@ddd)
                       WHEN CHARINDEX('DD',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'DD' collate Latin1_General_CS_AS ,@dd)
                       WHEN CHARINDEX('D',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'D' collate Latin1_General_CS_AS ,@d) END;

   SELECT @ResultVar = REPLACE(@ResultVar collate Latin1_General_CS_AS,'Monday' collate Latin1_General_CS_AS ,'monday')

   SELECT @ResultVar = CASE WHEN CHARINDEX('MONTH',@ResultVar) > 0 THEN  REPLACE(@ResultVar collate Latin1_General_CS_AS,'MONTH' collate Latin1_General_CS_AS,@month)
                       WHEN CHARINDEX('MMM',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'MMM' collate Latin1_General_CS_AS,@mmm)
                       WHEN CHARINDEX('MM',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'MM' collate Latin1_General_CS_AS,@mm)
                       WHEN CHARINDEX('M',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'M' collate Latin1_General_CS_AS,@m) END;


   SELECT @ResultVar = CASE WHEN CHARINDEX('YEAR',@ResultVar) > 0 THEN  REPLACE(@ResultVar collate Latin1_General_CS_AS,'YEAR' collate Latin1_General_CS_AS ,@year)
                       WHEN CHARINDEX('YYYY',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'YYYY' collate Latin1_General_CS_AS ,@yyyy)
                       WHEN CHARINDEX('YYY',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'YYY' collate Latin1_General_CS_AS ,@yyy)
                       WHEN CHARINDEX('YY',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'YY' collate Latin1_General_CS_AS ,@yy)
                       WHEN CHARINDEX('Y',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'Y' collate Latin1_General_CS_AS ,@y) END;

   SELECT @ResultVar = REPLACE(@ResultVar collate Latin1_General_CS_AS,'monday' collate Latin1_General_CS_AS ,'Monday')

   RETURN @ResultVar

 END;
 GO

Hope this helps!

Inverse format function

Following Larnu advice, I have replaced 'T' with an space, and injected ":" separators with the STUFF function, so the strings can now be converted to datetime using the 112 style.

convert(datetime,stuff(stuff(replace('20220406T123043','T',' '),12,0,':'),15,0,':'),112)

Thank you.

SQL Server Specific Date Format DD MM YYYY

I'd go with your first suggestion, seems the most appropriate;

Making some test data;

IF OBJECT_ID('tempdb..#Dates') IS NOT NULL DROP TABLE #Dates
GO
CREATE TABLE #Dates (OriginalDate datetime)
INSERT INTO #Dates (OriginalDate)
VALUES
('2016-01-01')
,('2016-05-06')
,('2016-08-09')
,('2016-12-25')

Query;

SELECT
OriginalDate
,REPLACE(CONVERT(varchar,OriginalDate,103),'/',' ') NewDate
FROM #Dates

Result;

OriginalDate                NewDate
2016-01-01 00:00:00.000     01 01 2016
2016-05-06 00:00:00.000     06 05 2016
2016-08-09 00:00:00.000     09 08 2016
2016-12-25 00:00:00.000     25 12 2016

formatting date on insert to sql?

Ideally, the data type of the column in the database is set to Date or DateTime. Basically, if you want to store a date, then use a date date type.

That being said, in VB6 you usually have to (at least temporarily) store the date as a string so there is almost always a string to date conversion that happens somewhere.

Will SQL let me insert a value in a format MM/DD/YYYY

Yes. But you should not do this. Instead, you should insert the date with the format "YYYYMMDD". Notice that there are no delimiters. The problem with mm/dd/yyyy is that it could accidentally be interpreted as the wrong date. For example, 1/2/2015 would be interpreted as Feb 1, 2015 if you lived in England, or Jan 2, 2015 if you live in the US. However, SQL Server will always interpret 20150102 and Jan 2, 2015.

Once you have the data stored the way you want in the database (as an actual date data type), you should actually return it as a date to your front end (either Access or VB6). In the front end, you should use the format command to display the date. The format command will use the regional settings of the computer to display dates the way the user wants to see it.

Ex:

txtDateOfBirth.Text = Format(rs.Fields.Item("DOB").value, "Short Date")

Doing things this way... you should never have problems with dates.

To convert date format in SQL Server

The DATENAME function will return the Name of the month

SELECT DATENAME(month,'2017-06-30 23:59:59.000')+' '+CAST(YEAR('2017-06-30 23:59:59.000') AS VARCHAR) 'Month Name'
SELECT DATENAME(mm,'2017-06-30 23:59:59.000')+' '+CAST(YEAR('2017-06-30 23:59:59.000') AS VARCHAR) 'Month Name'
SELECT DATENAME(m,'2017-06-30 23:59:59.000')+' '+CAST(YEAR('2017-06-30 23:59:59.000') AS VARCHAR) 'Month Name'

This will return

June 2017

If you want to get it from a table, then replace the table and column name in the below query.

SELECT DATENAME(month,ColumnName)+' '+CAST(YEAR(ColumnName) AS VARCHAR) 'Month Name' 
FROM TabeName

---

Related Topics