score:6
Accepted answer
Once you get your story straight, you'll want one of these:
-- if you really meant dd/mm/yyyy then:
SELECT CONVERT(DATETIME, '16/3/2012 4:39:26 PM', 103);
-- if you really meant mm/dd/yyyy then:
SELECT CONVERT(DATETIME, '3/16/2012 4:39:26 PM', 101);
But agreed with Madhivanan. Don't store dates using the wrong data type, and if you must, use an unambiguous format!
Read More
- Which datetime format to use SQL Server
- Convert string to SQL Server DateTime which should be in "yyyy-MM-dd hh:mm:ss" format
- Convert a SQL Server datetime to a shorter date format
- How to format datetime in SQL SERVER
- How does SQL Server decide format for implicit datetime conversion?
- SQL SERVER DATETIME FORMAT
score:-3
This will work.
DECLARE @dt varchar(100)='2012/3/16 4:39:26 PM'
select convert(datetime,@dt,101)
score:4
- Always use proper
DATETIME
datatype to store dates YYYYMMDD
andYYYYMMDD HH:MM:SS
are unambiguous date formats
For more information, refer this
More questions
- Which jar to use for connecting to MS SQL server
- Crystal Reports, which OLEDB provider to use for SQL Server
- When storing a datetime in sql server (datetime type), what format does it store it in?
- MS SQL Server 2012 exports datetime as script which it cannot import
- Convert Varchar Column to Datetime format - SQL Server
- Which SQL Server sql data type to use in order to preserve UTC date time
- How do I get EntityFrameworkCore generated SQL to use the right format for DateTime objects?
- Which settings provide SQL Server default datetime format?
- What serialization format should we use to store serializaed objects in a SQL Server database
- How to use SQL Server datetime type in Java
- What SQL Server DataType should I use for a Text field which will be JOINED, "LIKE'd" and Queried against a lot?
- Convert varchar in format mon-yy to datetime in SQL server
- What is the correct expression to use in order to format a date from "yyyyMMdd" to "M/d/yyyy" in SQL Server Reporting Services?
- Convert NSDate to Sql Server Datetime format
- How format datetime for sql server in laravel?
- How to use default DateTime values with SQL Server CE and Entity Framework
- Convert Oracle Datetime format query to MS SQL Server Format
- Date string to datetime SQL Server for a dash-delimited format
- DateTime format using t-sql string functions in SQL Server query
- How can I use Powershell to see which sql server databases have maintenance plans?
- In which scenario would you prefeer to use mysql over sql server or viceversa?
- Which Sql Server column type to use for a position in a video?
- SQL Server format file - can I use to generate create table statement?
- Which SQLCMD.exe to use (x64 or x86) -- SQL Server 2008?
More questions with similar tag
- How can I write queries without having to specify the schema name?
- How to pass optional parameter in stored procedure in my case in SQL Server?
- Saving a pem certificate in a SQL Server database
- Sql server greater than operator gives wrong result on datetime
- SQL SELECT Union SELECT FROM (Select...)
- Procedure expects parameter even though the parameter is provided
- Entity framework CREATEs against SQL Server DB nonnullable columns
- How to get SSRS Data Source credentials and open an SQL Connection in .NET
- Is there a single event type for ddl trigger that tracks all drop events in database (t-sql)?
- Query performance changes dramatically between two similar queries
- SQL Server: get top xx blog record from Umbraco by parameter
- SQL Server synonyms with concurrent execution
- Kind of cross join query with filtering
- Find out sql server hardware or speed test
- Convert Image to Base64String in SELECT query
- Row number over a partition and sliding window
- Is there a way to easily change the server name on several SSIS packages programmatically?
- Northwind.mdf - This database file is not compatible with the current instance of SQL Server (in Visual Studio 2017)
- How do I define a work week in SQL Server 2000?
- sp_executesql is slow with parameters
- SQL project monthly rent obligation on lease
- Insert concat of two columns back into one of the original columns
- Parse XML in SQL Server resulting in NULLs
- Conditional UPDATE statement with JOIN
- Does a stored procedure finish on "Execution Timeout Expired" (VB.Net)?
- SQL: remove all special char's from Timestamp field and convert as INT
- Truncate Table Within Transaction
- SQL Join Issue return DISTINCT Names
- Horrible sql server performance when capturing result in variable
- SQL Job Step to Compare File Names
Source:
stackoverflow.com