Need help in converting Nvarchar to Datetime

Question

Post reply

Need help in converting Nvarchar to Datetime

Bujji1987

SSC-Addicted

Points: 453
More actions
October 10, 2018 at 7:35 am

#379128

Hi Experts,
Please help me in Nvarchar to Datetime, my scenario is as below
I'm getting data in NVARCHAR and format is "03\15\2010 01" i want this to be in DATETIME format like data "03-15-2010 01:00:00.000".
I've tried below but no luck
DECLARE @ndate nvarchar(50) = N'2010/03/15 01'
SELECT CONVERT(datetime, stuff(stuff(stuff(@ndate,3,0,'/'),6,0,'/'),8,0,'/'), 101)
Expecting result to be like 2010-03-15 01:00:00.000
Thanks in Advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

SELECT CONVERT(DATETIME,REPLACE('03\15\2010 01','\','/')+':00:00')

--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)

Keith Oliver SSC Veteran Points: 288 More actions · Answer 2

DECLARE @ndate nvarchar(50) = N'2010/03/15 01'
SET @Ndate = @ndate + ':00:00'
SELECT CONVERT(datetime, @ndate, 101)

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

Keith Oliver - Wednesday, October 10, 2018 7:58 AM
DECLARE @ndate nvarchar(50) = N'2010/03/15 01'
SET @Ndate = @ndate + ':00:00'
SELECT CONVERT(datetime, @ndate, 101)

The OP has "\"s in the dates.

--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)

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 4

Bujji1987 - Wednesday, October 10, 2018 7:35 AM
Hi Experts,
Please help me in Nvarchar to Datetime, my scenario is as below
I'm getting data in NVARCHAR and format is "03\15\2010 01" i want this to be in DATETIME format like data "03-15-2010 01:00:00.000".
I've tried below but no luck
DECLARE @ndate nvarchar(50) = N'2010/03/15 01'
SELECT CONVERT(datetime, stuff(stuff(stuff(@ndate,3,0,'/'),6,0,'/'),8,0,'/'), 101)
Expecting result to be like 2010-03-15 01:00:00.000
Thanks in Advance

You've put:
NVARCHAR and format is "03\15\2010 01"
then you've put:
DECLARE @ndate nvarchar(50) = N'2010/03/15 01'
Which one is it?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Jonathan AC Roberts - Wednesday, October 10, 2018 8:55 AM
Bujji1987 - Wednesday, October 10, 2018 7:35 AM
Hi Experts,
Please help me in Nvarchar to Datetime, my scenario is as below
I'm getting data in NVARCHAR and format is "03\15\2010 01" i want this to be in DATETIME format like data "03-15-2010 01:00:00.000".
I've tried below but no luck
DECLARE @ndate nvarchar(50) = N'2010/03/15 01'
SELECT CONVERT(datetime, stuff(stuff(stuff(@ndate,3,0,'/'),6,0,'/'),8,0,'/'), 101)
Expecting result to be like 2010-03-15 01:00:00.000
Thanks in Advance
You've put:
NVARCHAR and format is "03\15\2010 01"
then you've put:
DECLARE @ndate nvarchar(50) = N'2010/03/15 01'
Which one is it?

Except for replacing backslashes with slashes, it won't actually matter. SQL Server will handle both without having to identify which format it's in IF you have the proper language settings.

SELECT CONVERT(DATETIME,REPLACE('03\15\2010 01','\','/')+':00:00')
,CONVERT(DATETIME,REPLACE('2010/03/15 01','\','/')+':00:00') --Of course, REPLACE is overkill for this one.

--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)

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 6

Jeff Moden - Wednesday, October 10, 2018 9:14 AM
Jonathan AC Roberts - Wednesday, October 10, 2018 8:55 AM
Bujji1987 - Wednesday, October 10, 2018 7:35 AM
Hi Experts,
Please help me in Nvarchar to Datetime, my scenario is as below
I'm getting data in NVARCHAR and format is "03\15\2010 01" i want this to be in DATETIME format like data "03-15-2010 01:00:00.000".
I've tried below but no luck
DECLARE @ndate nvarchar(50) = N'2010/03/15 01'
SELECT CONVERT(datetime, stuff(stuff(stuff(@ndate,3,0,'/'),6,0,'/'),8,0,'/'), 101)
Expecting result to be like 2010-03-15 01:00:00.000
Thanks in Advance
You've put:
NVARCHAR and format is "03\15\2010 01"
then you've put:
DECLARE @ndate nvarchar(50) = N'2010/03/15 01'
Which one is it?
Except for replacing backslashes with slashes, it won't actually matter. SQL Server will handle both without having to identify which format it's in IF you have the proper language settings.
SELECT CONVERT(DATETIME,REPLACE('03\15\2010 01','\','/')+':00:00')
,CONVERT(DATETIME,REPLACE('2010/03/15 01','\','/')+':00:00') --Of course, REPLACE is overkill for this one.

So this works:
SET LANGUAGE us_english; SELECT CONVERT(DATETIME,REPLACE('03\15\2010 01','\','/')+':00:00') ,CONVERT(DATETIME,REPLACE('2010/03/15 01','\','/')+':00:00') --Of course, REPLACE is overkill for this one.
But this doesn't:
SET LANGUAGE British; SELECT CONVERT(DATETIME,REPLACE('03\15\2010 01','\','/')+':00:00') ,CONVERT(DATETIME,REPLACE('2010/03/15 01','\','/')+':00:00') --Of course, REPLACE is overkill for this one.