How to convert varchar data type into datetime data type without data lose

Hi all,

i have stored date as varchar(30) data type and Date format is dd/MM/yyyy now i have to convert it into datatime data type can u help me in query ?

 i have 330 records and each recod contain the Date in format of dd/MM/YYYY but due to varchar data type i can not perform calculation on Date so i have to convert into datetime without data lose.


please help me in this regard.


Best Regards
RameezWaheed

 


life is name of learning!
Mark as an answer if it helps
0
Rameezwaheed
11/2/2008 6:17:17 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

8 Replies
820 Views

Similar Articles

[PageSpeed] 41

You can check these posts

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2039108&SiteID=17

 

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25080


Please remember to click “Mark as Answer” on the post that helps you

Best Regards
Brij Mohan
http://www.dotnetglobe.com
0
mohanbrij
11/2/2008 6:33:55 AM

 Hi,Rameezwaheed

You Can get Help From This

 

Declare @date as varchar(30)

set @date='22/10/2008'

select convert(datetime,@date,103) as [DateColumn]

 

 

Or for ur case

Select convert(datetime,[TestColumnInVarchar],103) as [DateColumn] from TestTable

 

 

 

 

 

PLZ MARK AS ANSWER IF IT HELP U.

THANKS.


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
11/2/2008 6:39:57 AM

 Hi,

    You can use convert function and following is the sample to convert varchar to datetime:

 

Declare @Var1 varchar(30)
Declare @Var2 datetime
Set
@Var1 = '16/10/2008' SET @Var2 = CONVERT(DateTime, @Var1, 103)
Select @Var2
You can use the CONVERT function directly in your query. Hope this helps you!!! 
 
-Sri
-------------------------------------------------
If this post was useful to you, please mark it as answer. Thank you!
0
ksridharbabuus
11/2/2008 6:47:22 AM

thanks for fast reply ,

 all of you are correct but when i run the following query

Select convert(datetime,[Date],103) as [Date] from mapplication

i got the folowing error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

what i have to do for resloving this issue .

may i have to create new table and then i will use convert function for inserting date or i can update the existing column .

Best Regards

Rameezwaheed


life is name of learning!
Mark as an answer if it helps
0
Rameezwaheed
11/2/2008 6:47:29 AM

Hi,Rameezwaheed

Rameezwaheed:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

That is be Because some of ur rows contain data aother than dd/mm/yyyy as u specified

So u should cosider it

 

 

These two will not show error

Declare @date as varchar(30)

set @date='22/10/2008'--dd/mm/yyyy

select convert(datetime,@date,103) as [DateColumn]

 

Declare @date as varchar(30)

set @date='2008/22/10'--yyyy/dd/mm

select convert(datetime,@date,103) as [DateColumn]

 

 

But these two will show error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

  

Declare @date as varchar(30)

set @date='10/22/2008'--mm/dd/yyyy

select convert(datetime,@date,103) as [DateColumn]

Declare @date as varchar(30)

set @date='2008/10/22'--yyyy/mm/dd

select convert(datetime,@date,103) as [DateColumn]

 

u r getting that error because

103 is the format to deal with dd/mm/yyyy

 

 

 

and so to rectify that kind of rows if they r in mm/dd/yyyy

U sholud use 101 like below

 

 

Declare @date as varchar(30)

set @date='10/22/2008'--mm/dd/yyyy

select convert(datetime,@date,101) as [DateColumn]

Declare @date as varchar(30)

set @date='2008/10/22'--yyyy/mm/dd

select convert(datetime,@date,101) as [DateColumn]

 

 

 

 

PLZ MARK AS ANSWER IF IT HELP U.

THANKS.


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
11/2/2008 1:27:08 PM

Hi,

    Can you please make sure that the date format is according to the style specified in the convert function.

In the example we used 103 indicates dd/mm/yyyy format.
 


-Sri
-------------------------------------------------
If this post was useful to you, please mark it as answer. Thank you!
0
ksridharbabuus
11/3/2008 3:55:32 AM

try the below query

 select cast((left(right(mydate,7),2) +'/' + left(mydate,2) + '/' + right(mydate,4)) as datetime) from stringdates

 

see the below script:

 
create table stringdates
(
    mydate varchar(100)
)

insert into stringdates values ('15/01/2007')
insert into stringdates values ('15/01/2006')
insert into stringdates values ('15/01/2005')
insert into stringdates values ('15/01/2004')
insert into stringdates values ('15/01/2003')

select cast((left(right(mydate,7),2) +'/' + left(mydate,2) + '/' + right(mydate,4)) as datetime) from stringdates

 


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
0
ramireddyindia
11/3/2008 4:10:39 AM

Thanks all ,

 

last query works for me again thanks for all .

so happy to learn .

 

 

Best regards

Rameezwaheed


life is name of learning!
Mark as an answer if it helps
0
Rameezwaheed
11/3/2008 7:13:19 AM
Reply:

Similar Artilces:

System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
After testing out the application i write on the local pc. I deploy it to the webserver to test it out. I get this error. System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Notes: all pages that have this error either has a repeater or datagrid which load data when page loading. At first I thought the problem is with the date, but then I can see that some other pages that has datagrid ( that has a date field) work just fine. anyone having this problem before?? hopefully you guys can help...

System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
After clicking my update button I'm receiving this error:    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been...

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Hi Guys, I've tried reseasrching this problem on the internet without success. I'm UK based and Classic ASP trained but having problems with asp.net (VB) Inserting "Date" Data into an inherited MS-SQL Server 2008 table's date field. 1.) In webconfig's System.Web section I have added the following: <globalization culture="en-GB" uiCulture="en-GB" /> ' so I'm now hoping all dates can be handled as dd/MM/yyyy! 2.) In SQL Server the "Default Value or Binding" for the quote_date field is set to '...

The conversion of a char data type to a datetime data type!!
hello all .. I have a form that includes two textboxes (Date and Version) .. When I try to insert the record I get the following error message .. seems that something wrong with my coversion (Data type)"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."   in my SQL database I have the date feild as datetime and the version as nvarchar(max) this is the code in the vb page .. Can you please tell me how to solve this problem?Imports System.Data.SqlClient Imports system.web.configuration ...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Hey, I have a big problem that i wanna search data from SQL by DateTime like thatselect * from test where recorddate='MyVariableWhichHoldDate'i use variable that holds Date info.i searched a lot infomation on net but there is no perfect solution. i know why this occur but there is no function to solve this problem. i used a lot of ways. it accept yyyy-mm-dd format but my variable format is dd-mm-yyyyy . is there any function for this problem? and any other solution.thanks for ur attentionregards Just do select * from table where dateField = '1-1-2005'. This will work..Nick The safest for...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
  Hi , i m inserting the values in the fallowing ways INSERT INTO LoginHistory([UserID],[LoginTime],[LoginIP])VALUES('NilCom1Ad01','13-04-2009 4:52:54 PM','127.0.0.1' ) Still i  m Geting errror  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated.Plz  help me  I'm wondering if your database is expecting month/day/year, in which case 13 would be causing your problem.  Change the date to 04-13-2009 and give it a try.C# <---> V...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Hello this is my code: ( I want to see if the date is in a month of a selected year)Dim command As New SqlCommand command.Connection = connection command.CommandType = CommandType.Text command.CommandText = "SELECT count(*) fROM tblRequests r WHERE r.StateID =4 AND r.CategoryID <>2 AND r.CompletionDate BETWEEN '" & year & "-" & teller & "-01' AND '" & year& "-" & teller + 1 & "-01'"Dim objaantal As Object = command.ExecuteScalar() Dim Incidents As Integer If Not objaantal Is No...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I still don't know what is happening with my application. I used before in another page the formview control and I didn't have the out of range Date error. It was normally. But now, I'm doing everything manually and I don't know why, but I'm with this problem. I need to safe this problem today... Here go my code!Thanks Raphael <%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQLClient" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> ...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Normal 0 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-b...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
 Please help me its give me the error on server but run successfully on my local  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime...

conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
  When I run this error I get this error  ' The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.' select distinct pro.prodname, pro.price, pro.description, pro.fromdate, pro.todate, pro.pic1 from products pro inner join booking on pro.category=booking.category where pro.category=1 and '12/11/2008' between pro.fromdate and pro.todate and '18/12/2008' between pro.fromdate and pro.todate and '12/11/2008' not between booking.checkin and booking.checkout and '18/12/2008' not between booking.chec...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
 Hello,When i run my project in VS IDE i dont get this below mentioned error but assoon as i put it in IIS of my server machine i get this error.I am using this application since months and wasn't getting any error. But recently when my admin made some changes in server..This error started coming. I have no help from my admin regarding this.  Let me know if i have to do some changes in c# code or in database or in windows server machine or in IIS or in crystal report properties !! The error is as follows : Normal 0 false false false ...

convert an sql datetime column to .net datetime data type in front end...?
I have a field (ldate) in a table that typically displays data in the following format:2007-11-12 20:30:47.000In the front end I want the time and the date separately. But as I try to cast it to a datetime object it is giving me a System.NullReferenceException. What I tried to do was just this: Protected Sub gvTopics_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvTopics.RowDataBound Dim img As Image = gvTopics.FindControl("imgIcon") Dim lbDate, lbTime As Label lbDate = gvTopics.FindControl(...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. #2
sombedoy can solve this problem:   my code:   '######################################## '# Create values for XML File # '########################################Dim command As New SqlCommand command.Connection = connection command.CommandType = CommandType.Text  Dim datum1, datum2 As DateTime   datum1 = teller6.ToString & "/01/" & jaar & " 0:00:00" datum2 = (teller6 + 1).ToString & "/01/" & jaar & " 0:00:00"   command.CommandText = "SELECT count(*) fROM tblRequests r WHER...

Web resources about - How to convert varchar data type into datetime data type without data lose - asp.net.sql-datasource

Is there a JQuery plugin to convert UTC datetimes to local user timezone?
If I have a tag: &lt;span class="utctime"&gt;2010-01-01 11:30 PM&lt;/span&gt; I would like a jquery script or plug in to convert every utctime ...


Targetprocess 3.7.3: Epic unit for Bug/Story cards, new DateTime functions in Webhook plugin
Webhook plugin supports new DateTime functions AddDays(DateTime date, int days) – Returns a new date with the number of days added. The number ...

Common Pitfalls when working with DateTime’s
In .NET, the DateTime structure provides us wonderful functionality, but this seemingly simple structure can cause a lot of headaches if you ...

Parsing Twitter Date format to .Net DateTime
Recently when I needed to parse the twitter datetime format into a .Net DateTime I found this post on the web showing how to do it. However, ...

C Sharp (programming language) - Wikipedia, the free encyclopedia
(pronounced see sharp ) is a multi-paradigm programming language encompassing strong typing , imperative , declarative , functional , generic ...

1753 - Wikipedia, the free encyclopedia
Language: English Afrikaans አማርኛ العربية Aragonés Asturianu Aymar aru Azərbaycanca বাংলা Bân-lâm-gú Basa Banyumasan Беларуская ‪Беларуская (тарашкевіца)‬ ...

Coded Smorgasbord: Schizophrenic Haiku Comments and More
... writes Malcom StandardResponse UnSubscribeNewsletterUserAccount( string opTinGUID,string email, string sellingRegion, string source, DateTime ...

Limburg.be - Welkom op de site van de provincie Limburg - Welkom_op_de_site_van_de_provincie_Limburg
visit wonen werken leven ontspannen infopunt home op trefwoord contactpersonen wegwijs sitemap print deze pagina Je bent hier: limburg.be Contact ...

Simples Ideias. Por Nando Vieira.
Simples Ideias Siga-me no Twitter Junho/2012 Usando o Vagrant como ambiente de desenvolvimento no Windows http://simplesideias.com.br/usando ...

Resources last updated: 1/19/2016 4:43:39 AM