Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql DateTime convert  problem

Posted on 2008-06-16
2
Medium Priority
?
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

604 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