Converting from varchar 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

General SQL Server Forums

New to SQL Server Programming

Converting from varchar to datetime

Author

Topic

boltosaurus
Starting Member

7 Posts

Posted - 2011-11-28 : 07:41:16

Hi guys

I have such problem:
I have a column of type nvarchar with values like 'Nov 27 2011' which as you understand represents date :).
Is there any way to parse these values to datetime format?

Thanks for the assistance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 07:43:08

if the regional settings of your server is english you can use

select cast(yourdatefield as datetime)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

boltosaurus
Starting Member

7 Posts

Posted - 2011-11-28 : 07:54:27

thanx for the reply

I got an exception:
"Conversion failed when converting datetime from character string."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 08:06:05

quote:
Originally posted by boltosaurus

thanx for the reply

I got an exception:
"Conversion failed when converting datetime from character string."

then that means you've some other format date values also in the field.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

boltosaurus
Starting Member

7 Posts

Posted - 2011-11-28 : 08:14:33

my fault - your solution works!
thanx a lot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 08:16:51

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-28 : 08:34:37

Note that this is language dependent and same code will not work if the language of the server is not English

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 08:39:24

quote:
Originally posted by madhivanan

Note that this is language dependent and same code will not work if the language of the server is not English

Madhivanan

Failing to plan is Planning to fail

I've already specified it

see suggestion at 11/28/2011 : 07:43:08

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-28 : 08:40:43

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

Note that this is language dependent and same code will not work if the language of the server is not English

Madhivanan

Failing to plan is Planning to fail

I've already specified it

see suggestion at 11/28/2011 : 07:43:08

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes I did not notice it

Madhivanan

Failing to plan is Planning to fail

boltosaurus
Starting Member

7 Posts

Posted - 2011-11-29 : 09:44:39

quote:
Originally posted by madhivanan

Note that this is language dependent and same code will not work if the language of the server is not English

Madhivanan

Failing to plan is Planning to fail

Hi!

What do you mean when you say "language of the server"? Do you mean COLLATION?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 11:14:27

quote:
Originally posted by boltosaurus

quote:
Originally posted by madhivanan

Note that this is language dependent and same code will not work if the language of the server is not English

Madhivanan

Failing to plan is Planning to fail

Hi!

What do you mean when you say "language of the server"? Do you mean COLLATION?

Nope. Not only that. The server where sql server is installed has a language setting also. to check it see

select @@language

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

boltosaurus
Starting Member

7 Posts

Posted - 2011-11-29 : 12:29:42

Got it
Thanx

quote:
Originally posted by visakh16

quote:
Originally posted by boltosaurus

quote:
Originally posted by madhivanan

Note that this is language dependent and same code will not work if the language of the server is not English

Madhivanan

Failing to plan is Planning to fail

Hi!

What do you mean when you say "language of the server"? Do you mean COLLATION?

Nope. Not only that. The server where sql server is installed has a language setting also. to check it see

select @@language

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 12:30:21

np
you're wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources