Convert date to smalldatetime - 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 date to smalldatetime

Author

Topic

kabal22
Starting Member

8 Posts

Posted - 2006-01-03 : 05:41:29

hi,

I need to convert a date with that format "1990-01-01T00:00:00.0000000+01:00" to that format "19/01/2005 9:10:00" ?

how can I do it ? I tried CAST('1990-01-01T00:00:00.0000000+01:00' as smalldatetime) but it doesn't work.

The error I got is : Syntax error converting character string to smalldatetime data type.

Help me please,
Kab

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-03 : 05:57:07

select dateadd(hour, 1, CAST('1990-01-01T00:00:00' as smalldatetime))

get rid of '.0000000' there's no need for that.

Go with the flow & have fun! Else fight the flow

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-03 : 05:59:36

because u are trying to convert a value which is not a valid datetime value.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 06:00:20

Where do you want to show the data?
If you use presentation layer, format it there

Refer this for more Date Formats
http://weblogs.sqlteam.com/derrickl/archive/2005/01/08/3959.aspx

Madhivanan

Failing to plan is Planning to fail

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-03 : 06:09:00

If you don't care about the timezone information


SELECT CONVERT(varchar(10), dt, 103) + ' ' + CONVERT(varchar(8), dt, 108)
FROM (
    SELECT CAST(LEFT('1990-01-01T00:00:00.0000000+01:00', 23) AS
      smalldatetime) AS dt
  ) AS A

kabal22
Starting Member

8 Posts

Posted - 2006-01-03 : 06:12:56

In fact, it is for un UPDATE query.

Kab

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-01-03 : 06:45:24

Are you going to change the underlying data type for that column, too?

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

kabal22
Starting Member

8 Posts

Posted - 2006-01-03 : 10:44:52

No, I just want to update some value taken.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources