SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Convert string to DateTime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

enga
Starting Member

Portugal
2 Posts

Posted - 12/27/2005 :  12:33:41  Show Profile  Reply with Quote
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

USA
366 Posts

Posted - 12/27/2005 :  12:55:12  Show Profile  Reply with Quote
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)

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/27/2005 :  13:01:06  Show Profile  Reply with Quote
I know Portugal uses a gender based language but did you really name your table "testes"?

Be One with the Optimizer
TG
Go to Top of Page

nosepicker
Constraint Violating Yak Guru

USA
366 Posts

Posted - 12/27/2005 :  13:21:28  Show Profile  Reply with Quote
And doing an insert into it must be really painful.
Might even cause the "log" to shrink (sorry, I couldn't resist).

Edited by - nosepicker on 12/27/2005 13:24:02
Go to Top of Page

enga
Starting Member

Portugal
2 Posts

Posted - 12/27/2005 :  13:47:07  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 12/28/2005 :  01:43:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

Switzerland
413 Posts

Posted - 12/28/2005 :  03:00:13  Show Profile  Visit Frank Kalis's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 12/28/2005 :  06:16:22  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000