SQL Server date format function

by

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.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. select yesterday date in where condition
  3. Import CSV file into SQL Server
  4. How to create a table from select query result in SQL Server 2008 [duplicate]
  5. Are there any disadvantages to always using nvarchar(MAX)?
  6. Creating an index on a table variable
  7. Is there a LastIndexOf in SQL Server?
  8. Get all table names of a particular database by SQL query?
  9. Turn off constraints temporarily (MS SQL)
  10. OR is not supported with CASE Statement in SQL Server
  11. How do I execute a stored procedure once for each row returned by query?
  12. Case in Select Statement
  13. Equivalent of Oracle’s RowID in SQL Server
  14. Concat field value to string in SQL Server
  15. Months between two dates
  16. Why can’t I use alias in a count(*) “column” and reference it in a having clause?
  17. How to store image in SQL Server database tables column [duplicate]
  18. How to find third or nᵗʰ maximum salary from salary table?
  19. SQL “between” not inclusive
  20. Benefits Of Using SQL Ordinal Position Notation?
  21. sql group by only rows which are in sequence
  22. SQL Server replace, remove all after certain character
  23. Does UNION ALL guarantee the order of the result set [duplicate]
  24. Inserting rows into a table with one IDENTITY column only [duplicate]
  25. How to find sum of multiple columns in a table in SQL Server 2005?
  26. How to retrieve field names from temporary table (SQL Server 2008)
  27. When using GETDATE() in many places, is it better to use a variable?
  28. Should I use the CASCADE DELETE rule? [duplicate]
  29. Why is RAND() not producing random numbers?
  30. SQL, How to Concatenate results?

Leave a Comment