Solved

sql DateTime convert  problem

Posted on 2008-06-16
2
296 Views
Last Modified: 2010-04-21
Hi
I'll simplify my problem , i have table with columm  "Issue_dt" smaldatetime
selct * from mytable
the result appear like the following
Issue_dt
-----------
2008-06-15 00:00:00
2008-06-12 13:49:00
2008-06-12 13:49:00
------------
but when i perform the follwong select
Select issue_dt,convert(nvarchar(10) ,issue_dt ,103) ,convert(nvarchar(10),'2009-08-03 00:00:00:000',103) from MyTable
the result comes as following
-------------------
2008-06-15 00:00:00      15/06/2008      2009-08-03
2008-06-12 13:49:00      12/06/2008      2009-08-03
2008-06-12 13:49:00      12/06/2008      2009-08-03
2008-06-12 13:49:00      12/06/2008      2009-08-03
---------------
my question is why the sql server convert the constant value  '2009-08-03 00:00:00:000'  to  2009-08-03 NOT to 03/08/2008 despite i use the same format in convert function ?
This cause a big problem in my VB.net application  because i sent a parameter to SP  with values like ''2009-08-03 00:00:00:000' to use it in Where condition to compare between the date only that stored in issue_dt column so when i use
convert(nvarchar(10),'2009-08-03 00:00:00:000',103)  it consider it as  2009-08-03 not as 03/08/2008 ? so how can i solve this problem
0
Comment
Question by:ali_alannah
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21794586
that is because your parameter in the third column IS already varchar and not datetime:


Select issue_dt,convert(nvarchar(10) ,issue_dt ,103) ,convert(nvarchar(10), convert(datetime, '2009-08-03 00:00:00:000', 120),103) from MyTable

Open in new window

0
 

Author Closing Comment

by:ali_alannah
ID: 31467651
Ooops !!!:)
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Recommendation vb6 to vb.net or others 14 112
SQL syntax in VB.net 5 30
Close tabpage of custom control 9 23
Get Top Visible Row of Datagridview 6 27
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now