Convert 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

General SQL Server Forums

New to SQL Server Programming

Convert to datetime

Author

Topic

idontknow
Starting Member

2 Posts

Posted - 2012-03-16 : 12:52:06

Hi everybody,

this part of my SQL command CONVERT(datetime, 2012-03-16 17:43:53.873, 121) throws the error "Incorrect error near '17'". What did I do wrong?

EDIT: Whoops, totally forgot the ' characters.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 12:58:26

SELECT CONVERT(datetime, '2012-03-16 17:43:53.873', 121)

Why would you add a format for it? That's meaningless

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

idontknow
Starting Member

2 Posts

Posted - 2012-03-16 : 13:05:02

It's not a SELECT, it's an INSERT, but okay.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 13:06:16

It's helpful to us when you post the entire SQL. In any case there is no need for a format. SQL Server stores datetime internally as 2 numbers

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-16 : 13:10:27

quote:
Originally posted by X002548

SELECT CONVERT(datetime, '2012-03-16 17:43:53.873', 121)

Why would you add a format for it? That's meaningless

Brett

8-)

It may not be needed in this case since the date string format is ODBC canonical and sql can handle that, but Meaningless? Far from it. It's always good form to specify the format when converting from a string to a date.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 13:25:08

And why is that?

SELECT ISDATE(CONVERT(datetime, '2012-03-16 17:43:53.873'))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-16 : 16:13:03

I'm not sure I understand your question. Are you asking why one would want to use good form and technique when programming? Or are you asking why it's good form to use a style specifier to denote what format the date string is in when converting it? Either way, the answer should be self-evident.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 16:41:01

>> good form and technique when programming

Really?

How the hell are you suppose to know the format of an input variable so you can supply the format?

This makes no sense

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-03-17 : 14:26:16

quote:
Originally posted by X002548

>> good form and technique when programming

Really?

How the hell are you suppose to know the format of an input variable so you can supply the format?

This makes no sense

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

If the date comes from different source with specific format in varchar datatype, it is needed to supply the format value if the date setting of the current server is different

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources