datetime issue - 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

datetime issue

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-09-12 : 09:14:34

Hello,

I need to convert a string to datetime...the thing is I use a middleware to process the sql. For that reason my format can't have spaces or : in it.

One year ago I used this format:

2012-09-12T13_23_56

it as perfect..but now it' ot working anymore...does anbody another format which fits my requirement?

In addition to that I tried to concat a query myself:

SELECT convert(datetime,'2012-09-12T13'+':'+'23'+':'+'56'+':'+'333')

But it didn't work. Can anybody help me?

Kind regards,

Lara

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-12 : 09:17:18

You should be able to use style 126 to convert and get a string without any spaces. For example:

SELECT CONVERT(VARCHAR(32),GETDATE(),126);

returns this:

2012-09-12T09:16:45.767

Edit: If you don't want the fractional part, use one of these to trim off that:

SELECT LEFT(REPLACE(CONVERT(VARCHAR(32),GETDATE(),126),':','_'),19);
SELECT REPLACE(CONVERT(VARCHAR(19),GETDATE(),126),':','_');

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 11:01:33

quote:
Originally posted by musclebreast

Hello,

I need to convert a string to datetime...the thing is I use a middleware to process the sql. For that reason my format can't have spaces or : in it.

One year ago I used this format:

2012-09-12T13_23_56

it as perfect..but now it' ot working anymore...does anbody another format which fits my requirement?

In addition to that I tried to concat a query myself:

SELECT convert(datetime,'2012-09-12T13'+':'+'23'+':'+'56'+':'+'333')

But it didn't work. Can anybody help me?

Kind regards,

Lara

see

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-13 : 02:25:10

<<
the thing is I use a middleware to process the sql
>>

What is that middleware? You should always pass date values using a proper DATE or DATETIME parameter

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources