how to convert navarchar to datetime

Question

Post reply

how to convert navarchar to datetime

komal145

SSCrazy Eights

Points: 9918
More actions
September 21, 2016 at 10:36 am

#330314

i have column senddate as "2016-08-10 05:29:27" as nvarchar (4000) destination is datetime.
how do i convert this to datetime.
convert(datetime,CAST(SendDate as datetime),112) but appends 000 at last
like '2016-08-10 05:29:27.000' do not need 000 at last

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

That is the precision level for datetime. If you want a lower level of precision use datetime2.

DATETIME2(0) would give you precision to the second.

Shamless self promotion - read my blog http://sirsql.net

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

It's not appending zeros, that's just the way it displays the value stored. Datetime is stored as 2 integers combined in an 8-byte value. One integer is used for the date and the other is used for the time.

It's fully explained in these articles:

http://sqlmag.com/sql-server/solving-datetime-mystery

http://www.sql-server-performance.com/2004/datetime-datatype/

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-are-dates-stored-in-sql-server/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

A "SendDate" as an NVARCHAR(4000)? Now that's a table that probably needs to be redesigned a bit. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

Jeff Moden (9/21/2016)
A "SendDate" as an NVARCHAR(4000)? Now that's a table that probably needs to be redesigned a bit. 😉

Well, depends on how far in the future SendDate might be. 😀

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

komal145 SSCrazy Eights Points: 9918 More actions · Answer 5

?? the table that has sendate is created by someone else , my table has datetime.

ScottPletcher SSC Guru Points: 101122 More actions · Answer 6

You can use CONVERT to format 120 to do that:

select '='+senddate+'=' AS [original char format], convert(varchar(19), SendDate, 120) AS [yyyy-mm-dd hh:mm:ss]

from (

select convert(nvarchar(4000), GETDATE(), 121) as senddate

) as test_using_current_datetime

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Be aware that conversion of varchars to datetimes by style "120" is locale dependent.

On different server settings you may get different results:

SET DATEFORMAT DMY

SELECT CONVERT(VARCHAR(20), CONVERT(datetime,CAST('2016-08-10 05:29:27' as datetime),112), 100)

SET DATEFORMAT YMD

SELECT CONVERT(VARCHAR(20), CONVERT(datetime,CAST('2016-08-10 05:29:27' as datetime),112), 100)

You have to remove dashes to make the outcome certain:

SET DATEFORMAT DMY

SELECT CONVERT(VARCHAR(20), CONVERT(datetime,CAST('20160810 05:29:27' as datetime),112), 100)

SET DATEFORMAT YMD

SELECT CONVERT(VARCHAR(20), CONVERT(datetime,CAST('20160810 05:29:27' as datetime),112), 100)

_____________
Code for TallyGenerator

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

komal145 (9/21/2016)
?? the table that has sendate is created by someone else , my table has datetime.

If you're converting to datetime in order to store in your table, you don't need to worry about the 0s on the end, since they won't change they way the date is stored (as Luis described). Make sure you know what format your dates are in before converting, as advised by Sergiy.

John

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

Sergiy (9/21/2016)
Be aware that conversion of varchars to datetimes by style "120" is locale dependent.
On different server settings you may get different results:
SET DATEFORMAT DMY
SELECT CONVERT(VARCHAR(20), CONVERT(datetime,CAST('2016-08-10 05:29:27' as datetime),112), 100)
SET DATEFORMAT YMD
SELECT CONVERT(VARCHAR(20), CONVERT(datetime,CAST('2016-08-10 05:29:27' as datetime),112), 100)
You have to remove dashes to make the outcome certain:
SET DATEFORMAT DMY
SELECT CONVERT(VARCHAR(20), CONVERT(datetime,CAST('20160810 05:29:27' as datetime),112), 100)
SET DATEFORMAT YMD
SELECT CONVERT(VARCHAR(20), CONVERT(datetime,CAST('20160810 05:29:27' as datetime),112), 100)

Actually, they don't need to be locale dependent. The format codes work both ways to prevent uncertain outcomes.

SET DATEFORMAT DMY

SELECT CONVERT(VARCHAR(20), CONVERT(datetime, '2016-08-10 05:29:27', 120), 100)

SET DATEFORMAT YMD

SELECT CONVERT(VARCHAR(20), CONVERT(datetime, '2016-08-10 05:29:27', 120), 100)

And the most adequate format to write dates is to use the full ISO 8601 format: yyyy-mm-ddThh:mi:ss.mmmZ

SET DATEFORMAT DMY

SELECT CONVERT(VARCHAR(20), CAST('2016-08-10T05:29:27' as datetime), 100)

SET DATEFORMAT YMD

SELECT CONVERT(VARCHAR(20), CAST('2016-08-10T05:29:27' as datetime), 100)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101122 More actions · Answer 10

Sergiy (9/21/2016)
Be aware that conversion of varchars to datetimes by style "120" is locale dependent.
On different server settings you may get different results:
SET DATEFORMAT DMY
SELECT CONVERT(VARCHAR(20), CONVERT(datetime,CAST('2016-08-10 05:29:27' as datetime),112), 100)
SET DATEFORMAT YMD
SELECT CONVERT(VARCHAR(20), CONVERT(datetime,CAST('2016-08-10 05:29:27' as datetime),112), 100)
You have to remove dashes to make the outcome certain:
SET DATEFORMAT DMY
SELECT CONVERT(VARCHAR(20), CONVERT(datetime,CAST('20160810 05:29:27' as datetime),112), 100)
SET DATEFORMAT YMD
SELECT CONVERT(VARCHAR(20), CONVERT(datetime,CAST('20160810 05:29:27' as datetime),112), 100)

I was converting to style 120, not from it. The cast from the original nvarchar I left as an implicit conversion, so that the most possible valid formats would work.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

komal145 SSCrazy Eights Points: 9918 More actions · Answer 11

@luis:Thank you for reply . I tried your query and the result from your query is :Aug 10 2016 5:29AM .

Which is not what i want. I needed "2016-08-10 04:29:07" this format

Sergiy SSC Guru Points: 110208 More actions · Answer 12

komal145 (9/22/2016)
@luis:Thank you for reply . I tried your query and the result from your query is :Aug 10 2016 5:29AM .
Which is not what i want. I needed "2016-08-10 04:29:07" this format

datetime values do not have format.

_____________
Code for TallyGenerator

PhilipC Hall of Fame Points: 3124 More actions · Answer 13

I have a similar issue where i have a varchar column that I need to convert to datetime or datetime2.

However when I run I get the following:

ALTER TABLE

ALTER COLUMN CreationTime datetime2

I get:

Msg 241, Level 16, State 1, Line 2

Conversion failed when converting date and/or time from character string.

The statement has been terminated.

Here are some sample values with all the dates attached. What do you I need to do to these dates to get them into a datetime/datetime2 format?

1/02/2010 1:48:33 PM

1/02/2010 3:36:10 PM

1/02/2010 8:12:47 AM

1/02/2010 8:39:15 AM

1/02/2010 8:39:27 AM

1/02/2010 8:40:25 AM

1/02/2011 1:16:46 PM

1/02/2011 1:32:15 PM

1/02/2011 10:15:26 AM

1/02/2011 10:51:48 AM

1/02/2011 12:09:39 PM

1/02/2011 12:25:18 PM

1/02/2011 5:48:26 PM

1/02/2011 8:01:25 AM

1/02/2011 9:13:42 AM

1/02/2011 9:32:37 AM

1/02/2011 9:59:00 AM

1/02/2012 1:21:31 PM

1/02/2012 1:58:23 PM

1/02/2012 10:52:41 AM

1/02/2012 11:40:07 AM

1/02/2012 11:56:03 AM

1/02/2012 11:58:02 AM

1/02/2012 11:59:38 AM

1/02/2012 12:49:05 PM

1/02/2012 2:07:48 PM

1/02/2012 2:33:43 PM

1/02/2012 2:43:51 PM

1/02/2012 2:54:44 PM

1/02/2012 3:10:28 PM

1/02/2012 4:02:22 PM

1/02/2012 8:25:37 AM

1/02/2012 8:37:48 AM

1/02/2012 8:41:50 AM

1/02/2012 8:42:03 AM

1/02/2012 9:01:58 AM

1/02/2012 9:35:15 AM

1/02/2013 1:27:27 PM

1/02/2013 1:30:23 PM

1/02/2013 1:37:14 PM

1/02/2013 1:37:28 PM

1/02/2013 1:37:42 PM

1/02/2013 1:38:05 PM

Sergiy SSC Guru Points: 110208 More actions · Answer 14

You need to read CAST and CONVERT on MSDN:

https://msdn.microsoft.com/en-NZ/library/ms187928.aspx

_____________
Code for TallyGenerator