Convert nvarchar to datetime

Question

Post reply

Convert nvarchar to datetime

Viewing 7 posts - 16 through 21 (of 21 total)

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

Nick Maroudas SSC Eights! Points: 977 More actions · Answer 1

Hi Colin,

I do agree with your explanation and what BOL offers.:D

donald.jones - asked a similar question to the original post and I was specifically answering his question.

(So by setting the dateformat he allows for the interpretation of the characters to allow the user to do further formatting within the query.)

Nick.

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 2

keywestfl9 (1/17/2008)
i have some fields in SQL Server table as nvarchar(50) and the user actually enters date (example : 02/05/07) now they want those fields to be converted to datetime or small datetime field.

Of course, in multinational companies, the more fundamental problem is getting the user to enter the date in the expected format before passing it to CONVERT, since just prompting with "Enter date in the form DD/MM/YY" usually isn't good enough (and it's usually a senior manager who types something different!). I usually use dropdown lists or calendar controls.

You gave a very good example of how one piece of text could mean several dates...

declare @input varchar(10)

set @input='02/05/07'

print CONVERT(datetime,@input,1) -- 5th February 2007

print CONVERT(datetime,@input,2) -- 7th May 2002

print CONVERT(datetime,@input,3) -- 2nd May 2007

Derek

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 3

donald.jones (1/21/2008)
I have the same problem, with my data imported as nvarchar in the format MMDDYYYY (01012008, for example.) I get an out-of-range datetime value when I attempt the alter column solution, which I gather means SQL doesnt recognize the field format as valid for conversion to datettime. Any ideas of now to get it into recognizableformat?

Although SQL server will automatically convert text to datetime (as several people said), it uses default assumptions about the format. In the case of a digit string '01012008', it will assume it's 'YYYYMMDD', hence you immediately get an error with '01012008' as there's no month 20!

For many formats, you can use CONVERT (see Books Online) and tell it exactly which format you've got. Unfortunately, 'MMDDYYYY' isn't included as a standard form so you need to use SUBSTRING to take the text apart and put it back together again.

declare @mydate nvarchar(8)

set @mydate = '01012008'

-- assume MMDDYYYY

-- insert '/'s (MM/DD/YYYY)

-- tell SQLserver it's US format with century (code 101)

print convert(datetime,substring(@mydate,1,2)+'/'+substring(@mydate,3,2)+'/'+substring(@mydate,5,4),101)

Derek

ed.nettestad SSC-Addicted Points: 480 More actions · Answer 4

Add a new column then populate it with the convert function

update table set newdatecol = convert(datetime,substring(olddate,1,2)+'/'+substring(olddate,3,2)+'/'+substring(olddate,5,4))

then delete old column and rename new to old.

Hope this helps.

Ed

Abrar Ahmad_ SSCarpal Tunnel Points: 4239 More actions · Answer 5

[font="Verdana"]Yep

I have a nvarchar field with a value '20100407132212' every database practitioner knows this format very well, but un-fortunately implicit conversion from this nvarchar data column to DATETIME format is not working, but it is confirmed by Microsoft that it works implicitly! but in my case explicit is also void. 😎

Any Bee over it?

And specially i don't want to use string tokens to get the final date...!

Thanks

[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Abrar Ahmad_ (5/7/2010)
[font="Verdana"]Yep
I have a nvarchar field with a value '20100407132212' every database practitioner knows this format very well, but un-fortunately implicit conversion from this nvarchar data column to DATETIME format is not working, but it is confirmed by Microsoft that it works implicitly! but in my case explicit is also void. 😎
Any Bee over it?
And specially i don't want to use string tokens to get the final date...!
Thanks
[/font]

SET DATEFORMAT YMD

DECLARE @CHARDATE NVARCHAR(19)

SET @CHARDATE = '20100407132212'

SET @CHARDATE = STUFF(STUFF(STUFF(STUFF(STUFF(@CHARDATE, 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':'), 17, 0, ':')

SELECT @CHARDATE

-- Result: 2010-04-07 13:22:12

DECLARE @DateDate DATETIME

SET @DateDate = @CHARDATE

SELECT @DateDate

-- Result: 2010-04-07 13:22:12.000

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Abrar Ahmad_ SSCarpal Tunnel Points: 4239 More actions · Answer 7

Chris Morris-439714 (5/7/2010)
Abrar Ahmad_ (5/7/2010)
[font="Verdana"]Yep
I have a nvarchar field with a value '20100407132212' every database practitioner knows this format very well, but un-fortunately implicit conversion from this nvarchar data column to DATETIME format is not working, but it is confirmed by Microsoft that it works implicitly! but in my case explicit is also void. 😎
Any Bee over it?
And specially i don't want to use string tokens to get the final date...!
Thanks
[/font]
SET DATEFORMAT YMD
DECLARE @CHARDATE NVARCHAR(19)
SET @CHARDATE = '20100407132212'
SET @CHARDATE = STUFF(STUFF(STUFF(STUFF(STUFF(@CHARDATE, 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':'), 17, 0, ':')
SELECT @CHARDATE
-- Result: 2010-04-07 13:22:12
DECLARE @DateDate DATETIME
SET @DateDate = @CHARDATE
SELECT @DateDate
-- Result: 2010-04-07 13:22:12.000

[font="Verdana"]

Thank you for your time and absolutely u didnt token the string with substrings(), rights() and lefts() but you used STUFF instead. :w00t:

I was thinking of some conversions to get the required result but it isnt possible i guess.

[/font]