CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
hitesh.gawhade
hitesh.gawhade

Reputation: 347

Convert inconsistent string to sql datetime

I am having date field in following format : Dec 8 2013 9:08AM (it's an nvarchar field) I need to convert this into more consistent and readable date format (e.g. yyyy-mm-dd). I tried using following query to convert it into different formats but its not working and returning same string :

CONVERT(char(11), mydate, 127)

Can someone please help?

Upvotes: 0

Views: 400

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

First of all, you will want to convert to DATETIME datatype. This is the database's datatype appropriate for storing date/time information. This has nothing to do with a format, it is just how the data is stored.

Example: The number 12345.6 can be shown as 12345.6 or as 12,345.60 or even 12.345,60 in another language. When you select a numeric value from the database the dbms uses the defualt format in order to show the value, but you can also specify an output format. Stored is just the value. Same with dates. If you want them shown as 'yyyy-mm-dd' then use this as the database default or specify the desired format in your select clause.

SQL Server's date conversion is very weak. One would expect a function where you simply specify the format used, but such is not available. The format you are using in your nvarchar field is an American Format, as far as I am aware. So make sure to have your database set to America, and things like 'DEC' for december and 'AM' for "before noon" will be recognized as default by CONVERT and you can simply use:

CONVERT(DATETIME, mydate)

If your system is not set appropriately (say it is set to GERMANY instead) and you don't have the right to change it, then CONVERT will not work, because 'DEC' may not be a known abbreviation for the months name (that would be 'DEZ' in German for instance) and also 'AM' may mean nothing to the dbms in that situation. Then you would have to use string manipulation functions to construct a valid date from the string given.

Upvotes: 1

Arindam Nayak
Arindam Nayak

Reputation: 7462

You can achieve this using following.

declare @dt as varchar(100)
set @dt = 'Dec 8 2013 9:08AM'

declare @conv datetime
select @conv = CONVERT(datetime, @dt)
select CONVERT(varchar(10),@conv,127) -- this is the result. output - 2013-12-08

Upvotes: 0

Related Questions