Solved

sql DateTime convert  problem

Posted on 2008-06-16
2
293 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
Comment Utility
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
Comment Utility
Ooops !!!:)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

15 Experts available now in Live!

Get 1:1 Help Now