HELP! Convert nvarchar to datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

HELP! Convert nvarchar to datetime

Author

Topic

MiguelFT
Starting Member

6 Posts

Posted - 2010-09-01 : 06:51:02

Hi all

I need help.

I want convert a nvarchar to datetime.

In this moment, i have in that "field":

2009-07-14 05:50:15

and I want..

14/07/2009 05:50:15..

Can your help me?

Thanks!!!

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-09-01 : 06:57:33

Do u need to format the date? or want to cast it as datetime?

Formatted date should be in varchar/nvarchar datatype

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

MiguelFT
Starting Member

6 Posts

Posted - 2010-09-01 : 07:04:07

I have a type nvarchar that contains (for example)

2009-07-14 05:50:15

and I want type datetime and format:

14/07/2009 05:50:15

Edit: Thanks!!!!

matty
Posting Yak Master

161 Posts

Posted - 2010-09-01 : 07:07:30

[code]
declare @p nvarchar (100)
set @p='2009-07-14 05:50:15'
select @p
select CONVERT(VARCHAR(20),CONVERT(DATETIME,@p),103) + ' ' +
CONVERT(VARCHAR(20),CONVERT(DATETIME,@p),108)
[/code]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-09-01 : 07:11:21

quote:
Originally posted by MiguelFT

I have a type nvarchar that contains (for example)

2009-07-14 05:50:15

and I want type datetime and format:

14/07/2009 05:50:15

Edit: Thanks!!!!

Datatime data type have fixed format. If you change the format it should be converted to Varchar/Nvarchar.

Better you cast as datetime in backend and format it in front end.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-09-01 : 07:18:08

quote:

Originally posted by matty


declare @p nvarchar (100)
set @p='2009-07-14 05:50:15'
select @p
select CONVERT(VARCHAR(20),CONVERT(DATETIME,@p),103) + ' ' + 
       CONVERT(VARCHAR(20),CONVERT(DATETIME,@p),108)

This result should be varchar, not a datetime.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

MiguelFT
Starting Member

6 Posts

Posted - 2010-09-01 : 07:30:27

quote:
Originally posted by senthil_nagore

quote:
Originally posted by MiguelFT

I have a type nvarchar that contains (for example)

2009-07-14 05:50:15

and I want type datetime and format:

14/07/2009 05:50:15

Edit: Thanks!!!!

Datatime data type have fixed format. If you change the format it should be converted to Varchar/Nvarchar.

Better you cast as datetime in backend and format it in front end.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

In this moment, I have a varchar... but I want a datetime with this format... (14/07/2009 05:50:15) do you understand?

Thanks!

MiguelFT
Starting Member

6 Posts

Posted - 2010-09-01 : 07:32:20

My "field" is varchar and contains (for example) 2009-07-14 05:50:15

I want a datetime for have this format: 14/07/2009 05:50:15

MiguelFT
Starting Member

6 Posts

Posted - 2010-09-01 : 07:44:54

I try for example:

select CONVERT(DATETIME,fecha,103) + ' ' +
CONVERT(DATETIME,fecha,108)
from dbo.TMP_BANDEJA
where ca2='695250806'

and SQL SERVER said always (in spanish):

"La conversión del tipo de datos char a datetime produjo un valor datetime fuera de intervalo." in english:

The conversion of char data type to datetime datetime value was out of range. (Translate Google) :P

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 07:45:21

Hi miguelFT.

Let me try and explain.

a DATETIME type is just a number. It doesn't have a 'format'. (it's actually stored as a count of milliseconds from a particular date)

This is how you would convert your NVARCHAR to a DATETIME


DECLARE @nvarcharField NVARCHAR(50)

SET @nvarcharField = N'2009-07-14 05:50:15'

SELECT CAST(@nvarcharField AS DATETIME)

If you cut and paste that code into a management stuido query window you will probably see:


2009-07-14 05:50:15.000

As the result -- this is just a *human readable* representation of the DATETIME

If you have a DATETIME field then you can CONVERT it to a VARCHAR field with formatting. However that's not generally a good idea because your application layer will cope better with the DATETIME than the returned VARCHAR.

If you absolutely need to do what you say you do then you can do something like this:


DECLARE @dateValue NVARCHAR(50)
SET @dateValue = N'2009-07-14 05:50:15'

-- Display as op required (14/07/2009 05:50:15)
SELECT
	CONVERT(
		CHAR(19)
		, CONVERT(
			DATETIME
			, @dateValue
			)
		, 120
	)

if you look up the CONVERT function in your reference (press f1 in management studio), you'll see a table of formatting options for dates.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 07:47:32

For the code you posted try this:


SELECT CONVERT(CHAR(19), CONVERT(DATETIME, fecha), 120)
FROM
	dbo.TMP_BANDEJA
WHERE
	ca2='695250806'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

MiguelFT
Starting Member

6 Posts

Posted - 2010-09-01 : 08:09:50

I Finded:

select CONVERT(varchar, CONVERT(datetime, substring(fecha,1,10), 101), 103) + substring(fecha,11,20)
from dbo.TMP_BANDEJA
where ca2='695250806'

FIXED!!!

Thanks for all

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-09-01 : 08:11:39

quote:
Originally posted by MiguelFT

I Finded:

select CONVERT(varchar, CONVERT(datetime, substring(fecha,1,10), 101), 103) + substring(fecha,11,20)
from dbo.TMP_BANDEJA
where ca2='695250806'

FIXED!!!

Thanks for all

But this result the output as varchar,not as datatime.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

CSears
Starting Member

39 Posts

Posted - 2010-09-01 : 12:36:09

quote:

But this result the output as varchar,not as datatime.

I would like to remind you that datetime does not have a format as was mentioned earlier. It's a numeric count of milliseconds from a reference date. Just because when you select it, it displays in a certain format, that does not mean that is how the information is saved. If you want to have the date look a certain way, it's common to convert the datetime variable to a varchar or nvarchar using the CONVERT function with the desired format. An example of this can be found here:

[url]http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources