About Converting Datetime to Date

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

About Converting Datetime to Date

Author

Topic

sneupane
Starting Member

28 Posts

Posted - 2010-01-27 : 17:09:54

I am a beginner in MS SQL. I have linked the Microsoft Navision Database with SQL server 2005 using ODBC driver. For an example I created a query and it resulted the order_Date field as

2009-10-10 00:00:00.000

I just need the 2009-10-10 part and do not need the remaining part of date. I tried using commands such as

Convert(char(10),[Order_Date],101). It did not work.

What could be the best way to solve the problem.

Thanks
SAR

SAROJ

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-27 : 17:28:00

Try style 120 instead of 101: Convert(char(10),[Order_Date],120)

Can I ask why you want to convert your wonderfull date into a character string?

sneupane
Starting Member

28 Posts

Posted - 2010-01-27 : 17:40:27

Coz I wanted to query the Date using following
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

DBCC TRACEON(8765)

Select *
From OPENQUERY([NAVISION], 'select Last_Date_Modified from Customer where Last_Date_Modified = 2010-10-10')

RETURN

And I got an error

OLE DB provider "MSDASQL" for linked server "NAVISION" returned message "[Simba][SimbaEngine ODBC Driver]Error in predicate: %sLast_Date_Modified = 2010 - 10 - 10".
OLE DB provider "MSDASQL" for linked server "NAVISION" returned message "[Simba][SimbaEngine ODBC Driver]Incompatible types in predicate.".
Msg 7321, Level 16, State 2, Line 4
An error occurred while preparing the query "select Last_Date_Modified from Customer where Last_Date_Modified = 2010-10-10" for execution against OLE DB provider "MSDASQL" for linked server "NAVISION".

SAROJ

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-27 : 17:47:39

I'm not an expert on OPENWUERY, but I think you need to encapsulate your date with single quotes. You can do that a couple of ways, here are two:

Select * 
From OPENQUERY([NAVISION], 'select Last_Date_Modified from Customer where Last_Date_Modified = ''2010-10-10''')

Select * 
From OPENQUERY([NAVISION], 'select Last_Date_Modified from Customer where Last_Date_Modified = ' + CHAR(39) + '2010-10-10' + CHAR(39))

sneupane
Starting Member

28 Posts

Posted - 2010-01-27 : 17:55:13

GR8!thanks a lot Lamprey. It worked. I don't have to make it string anymore.

SAROJ

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-28 : 03:15:00

As suggested, its not recommended to change datatype of date field for formatting. formatting should be done at front end using formatting functions.

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 03:36:15

"'2010-10-10'"

Note that for dates to be presented as strings and be implicitly converted by SQL a) unambiguously and b) independent of the locale of the server there must be no hyphens - i.e.


'20101010'

Hyphens are OK if you also have the TIME component:


'2010-10-10T01:02:03'

Very confusing, and works either way MOST of the time (but NOT all the time

)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-28 : 04:08:16

<<
works either way MOST of the time (but NOT all the time
>>

What do you mean by this?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 04:11:27

I meant that it depends on the Locale of the server. So move the application to another server and it stops working

I quite frequently come across servers that don't allow yyyy-mm-dd - so I don't think the locale is that rare, although I expect it is not the USA locale, so probably not seen that much over-the-pond.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-28 : 04:13:13

quote:
Originally posted by Kristen

I meant that it depends on the Locale of the server. So move the application to another server and it stops working

I quite frequently come across servers that don't allow yyyy-mm-dd - so I don't think the locale is that rare, although I expect it is not the USA locale, so probably not seen that much over-the-pond.

What happens when include time part preceded by T?
I think it would work well

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 04:32:18

Yes, the Date plus T plus Time is fine on servers with those locales

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources