Convert string to DateTime - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Convert string to DateTime

Author

Topic

enga
Starting Member

2 Posts

Posted - 2005-12-27 : 12:33:41

Hi!
I'm trying to insert a string in a table with a datetime column, i know that i must convert it,
so i was doing some experiments and manage to convert using style 112:
insert into testes values(1,convert(datetime,'20040508',112))

but what i really wanted was to insert with style 100
so the obvius(for me) was: convert(datetime,'Dec 04 2000 03:04PM',100) or
even convert(datetime,'12 04 2000 03:04PM',100)

But the only thing i get is "Syntax error converting datetime from character string".
Can anyone help me?

Greets
Pedro Barbosa

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-12-27 : 12:55:12

If your target column is datetime datatype, you don't need to convert your string - SQL Server will do an automatic conversion to datetime, as long as it is a valid date. If you want to convert it anyway, you should convert to datetime datatype, not style 112, 100, or any other style, like this:

CONVERT(datetime, '20040508')

Finally, to actually convert to any other style, you have to convert to varchar or char datatype, starting from datetime datatype. So if you have a string like '20040508' and you want to convert it to style 100, you need to do a double conversion like this:

CONVERT(varchar, CONVERT(datetime, '20040508'), 100)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-27 : 13:01:06

I know Portugal uses a gender based language but did you really name your table "testes"?

Be One with the Optimizer
TG

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-12-27 : 13:21:28

And doing an insert into it must be really painful.

Might even cause the "log" to shrink (sorry, I couldn't resist).

enga
Starting Member

2 Posts

Posted - 2005-12-27 : 13:47:07

the table is named testes because that's what i'm doing,i'm testing the insert of a string into a datetime column

About a direct insert of a string into the table it worked, don't know why wasn't working, but now it's fine!

Thanks

Pedro Barbosa

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-28 : 01:43:49

When inserting the dates, use ISO format to avoid conflict with local settings. Dont use convert functions.

Doesnt this work?

Insert into testes values(1,'20040508')

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-12-28 : 03:00:13

See if this helps: http://www.karaszi.com/SQLServer/info_datetime.asp

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

Kristen
Test

22859 Posts

Posted - 2005-12-28 : 06:16:22

Note that although this syntax raised no error:

SELECT convert(datetime,'20040508',112)

the thrid parameter is ignored, as it is only used for converting FROM DateTime TO varchar/string/...

If you need to force SQL Server to enterpret a "free format string date" in a particular way then use:

SET DATEFORMAT [/i]ymd[/i]

SELECT convert(datetime,'20040508')

But as Maddy says you would be well advised to use ISO format dates as moving to another server, or some change to the Locale of your current server, will screw everything up royally otherwise!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources