How to convert a column of "Apr 14 2004 12:00AM" nvarchar format to "14-Apr-2004" nvarchar format in a table?

Question

I have couple columns with "Apr 14 2004 12:00AM" nvarchar format in a table. I need to convert to "14-Apr-2004" nvarchar format. How can I do that? These are not in datetime format, and I have to get the 12:00am timing out of the column. There are many columns with many rows of data. Hope someone give me some idea, thanks!

Answer 1

Hi LadyCarol,

Two short term solutions:
1. Use a nested CONVERT that first converts the nvarchar value to datetime (use a style parameter to make sure that SQL Server uses the correct format), the convert the result back to nvarchar (again, with style parameter to force the required format).
2. Use a combination of SUBSTRING and string concatenation to cut the needed parts out of your string and add them back in the correct order.

One long term solution:
* Change your table to store datetime data in a date time format, and change your client to receive data from SQL Server in datetime format and format it according to the user's preferences.
  -- Hugo Kornelis
SQL Server MVP

Answer 2

try like this..

select REPLACE( CONVERT(varchar,CAST(mydate as datetime),106),' ','-') from TableName

Answer 3

In SQL Server 2008 you can use the DATE data type to get rid of the timepart.

For string formatting consider:

DECLARE @dt datetime = getdate()
SELECT REPLACE(LEFT(CONVERT (nvarchar, @dt, 113),11),' ','-')
-- 02-Nov-2010

Datetime conversion, date functions link:

http://sqlusa.com/bestpractices/datetimeconversion/

 

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Answer 4

try like this..

select REPLACE( CONVERT(varchar,CAST(mydate as datetime),106),' ','-') from TableName

 

I really need to update the data in the database.....your solution is just temporary data query....

Answer 5

you can convert that into updates statement.  But why you are maintaining an nvarchar column??? Its always useful if you maintain a datetime column. It not only saves space, it also saves from any conversion errors, easy manipulations and performance also(if any indexes). I strongly recommend an datetime column.

even i believe, your table should have some scrap data. so you will be getting error "conversion failed from nvarchar to datetime" or something. Its always better if u maintain an datetime column.

update tablename set mydate = REPLACE( CONVERT(varchar,CAST(mydate as datetime),106),' ','-') 

Answer 6

Hi LadyCarol,

Two short term solutions:
1. Use a nested CONVERT that first converts the nvarchar value to datetime (use a style parameter to make sure that SQL Server uses the correct format), the convert the result back to nvarchar (again, with style parameter to force the required format).
2. Use a combination of SUBSTRING and string concatenation to cut the needed parts out of your string and add them back in the correct order.

One long term solution:
* Change your table to store datetime data in a date time format, and change your client to receive data from SQL Server in datetime format and format it according to the user's preferences.
  -- Hugo Kornelis
SQL Server MVP

I need to let user input data in the field too, so your long term solution would not work..I have to make the column be in nvarchar so easily to store what user input in my program.

Answer 7

Hi LadyCarol,

I need to let user input data in the field too, so your long term solution would not work..I have to make the column be in nvarchar so easily to store what user input in my program.

In that case, any solution you use to reformat the dates is bound to fail. If you accept everything the user enters, you will invariably end up with garbage in your tables. And the garbage in garbage out principle ensures that this will lead to garbage in your reports.

Making the column datetime will indeed cause data entry errors when the users enter invalid data, but in my opinion, that is much better than to store whatever nonsense someone puts in - you'll pay the price later, when you find you miss important information.
 -- Hugo Kornelis
SQL Server MVP

Answer 8

I need to let user input data in the field too, so your long term solution would not work..I have to make the column be in nvarchar so easily to store what user input in my program.

   You need to let user input data in the field. But not any kind of data. Usually the practise, is while taking an date input, providing an calendar control to user, from that, user will choose the date.  usually any applications will handle these like

1. They will show a read-only textbox, followed by a small Button. (like "..")

2. When user clicks that, calendar control will popup. user selects date from that control.

3. When he selects, it will close, and the date will be populated in read-only textbox.

Finally when clicks submit button, we will convert it as datetime and store into database.

This is the usual pracise. 

Answer 9

hi,

If I have a table data as below.

pls ignore columnname.

ID hdcasename
----------- ----------------------------------------------------------------------------------
4 Apr 14 2004 12:00AM
5 Apr 15 2005 12:00AM
9 Aug 20 2005 12:00AM
1 Feb 14 2005 12:00AM
8 July 15 2005 12:00AM
7 June 15 2005 12:00AM
2 Mar 10 2005 12:00AM
3 Mar 14 2005 12:00AM
6 May 15 2005 12:00AM
10 Sep 20 2005 12:00AM

then you can write query to update your data like,

UPDATE Detailstbl
SET hdcasename = REPLACE(CONVERT(NVARCHAR,CAST(hdcasename AS DATETIME),106),' ','-')
WHERE CAST(hdcasename AS DATETIME) BETWEEN CAST('Apr 14 2004 12:00AM' AS DATETIME) 
										AND CAST('Sep 20 2005 12:00AM' AS DATETIME)

Here you can change the dates in BETWEEN in WHERE clause as per your data.You can use these as parameters.Here I have taken only one column of date field.same logic will be applied to another column as well.

the Result will be

ID hdcasename
----------- ----------------------------------------------------------------------------------
2 10-Mar-2005
4 14-Apr-2004
1 14-Feb-2005
3 14-Mar-2005
5 15-Apr-2005
8 15-Jul-2005
7 15-Jun-2005
6 15-May-2005
9 20-Aug-2005
10 20-Sep-2005

Thanks

Answer 10

I need to let user input data in the field too, so your long term solution would not work..I have to make the column be in nvarchar so easily to store what user input in my program.

That is a datetime nuclear winter scenario: column polluted with different date formats and possibly invalid dates. Note that a valid UK date 31/10/2016 is invalid US date. Your best bet is DATE data type (SQL Server 2008 and on) or datetime format (pre-2008). There are ways to ignore the time from datetime if not needed like:

DECLARE @dt datetime = getdate()
SELECT DateAndTime    = @dt
    ,DateOnlyString  = REPLACE(LEFT(CONVERT (nvarchar, @dt, 113),11),' ','-')
    ,DateWithMidnight1 = DATEADD(day, DATEDIFF(day,0,GETDATE()), 0)
    ,DateWithMidnight2 = CONVERT(DATETIME,CONVERT(int,@dt))
    ,DateWithMidnight3 = CONVERT(DATETIME,CONVERT(BIGINT,@dt) & (POWER(Convert(bigint,2),32)-1))
/* DateAndTime	DateOnlyString	DateWithMidnight1	DateWithMidnight2	DateWithMidnight3
2010-11-02 07:24:13.087	02-Nov-2010	2010-11-02 00:00:00.000	2010-11-02 00:00:00.000	2010-11-02 00:00:00.000
*/

Related link: http://www.sqlusa.com/bestpractices2005/centurydateformat/

 

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM