How to Convert Char to Datetime
in [Programming SQL]
|
Prev: DB Connection Pooling
Next: TOP clause
From: Justin D. on 6 Apr 2010 13:46 Hello. I am trying to use DATEDIFF here. DATEDIFF(day, getdate(),CONVERT(varchar, tblMain.dtOpen, 101)) I got the following error: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." It appears that I need to convert "tblMain.dtOpen" into datetime format to calculate DATEDIFF, correct? How can I convert Char to Datetime? Thanks in advance. Justin
From: Plamen Ratchev on 6 Apr 2010 13:59 What is the data type of the column dtOpen. If character data type, then what is the format? You can try: SELECT DATEDIFF(DAY, CURRENT_TIMESTAMP, CONVERT(DATETIME, CASE WHEN ISDATE(tblMain.dtOpen) = 1 THEN tblMain.dtOpen END, 101)) -- Plamen Ratchev http://www.SQLStudio.com
From: RJ Roberts on 6 Apr 2010 14:49 Judging by your naming it looks like dtOpen is a column that possibly is already defined as date? If so there is no need to convert to anything DATEDIFF(day, getdate(),tblMain.dtOpen) If dtOpen's data type is not a date then you need to convert or cast to a date format DATEDIFF(day, getdate(),cast(tblMain.dtOpen as DateTime)) -- RJ Roberts DB Architect/Developer "Justin D." wrote: > Hello. > > I am trying to use DATEDIFF here. > DATEDIFF(day, getdate(),CONVERT(varchar, tblMain.dtOpen, 101)) > > I got the following error: > "The conversion of a char data type to a datetime data type resulted in an > out-of-range datetime value." > > It appears that I need to convert "tblMain.dtOpen" into datetime format to > calculate DATEDIFF, correct? > > How can I convert Char to Datetime? > > Thanks in advance. > Justin >
From: Sylvain Lafontaine on 6 Apr 2010 14:56 First, you have wrote "varchar" instead of "datetime' for the first parameter inside the Convert function. Second, make sure that the format of the dates as stored in the tblMain.dtOpen field is compatible with the format 101. In particular, make sure that order for the month and the day is correct. Without knowing what's exactly in this field, it's hard to tell anything more precise. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "Justin D." <JustinD(a)discussions.microsoft.com> wrote in message news:11F7ED24-DA18-4B7E-8A10-9BBB203D56B0(a)microsoft.com... > Hello. > > I am trying to use DATEDIFF here. > DATEDIFF(day, getdate(),CONVERT(varchar, tblMain.dtOpen, 101)) > > I got the following error: > "The conversion of a char data type to a datetime data type resulted in an > out-of-range datetime value." > > It appears that I need to convert "tblMain.dtOpen" into datetime format to > calculate DATEDIFF, correct? > > How can I convert Char to Datetime? > > Thanks in advance. > Justin >
From: Justin D. on 6 Apr 2010 17:07
The data type is, actually, Varchar 50 and it shows up as 2008-03-24 00:00:00 "Sylvain Lafontaine" wrote: > First, you have wrote "varchar" instead of "datetime' for the first > parameter inside the Convert function. > > Second, make sure that the format of the dates as stored in the > tblMain.dtOpen field is compatible with the format 101. In particular, make > sure that order for the month and the day is correct. > > Without knowing what's exactly in this field, it's hard to tell anything > more precise. > > -- > Sylvain Lafontaine, ing. > MVP - Windows Live Platform > Blog/web site: http://coding-paparazzi.sylvainlafontaine.com > Independent consultant and remote programming for Access and SQL-Server > (French) > > > "Justin D." <JustinD(a)discussions.microsoft.com> wrote in message > news:11F7ED24-DA18-4B7E-8A10-9BBB203D56B0(a)microsoft.com... > > Hello. > > > > I am trying to use DATEDIFF here. > > DATEDIFF(day, getdate(),CONVERT(varchar, tblMain.dtOpen, 101)) > > > > I got the following error: > > "The conversion of a char data type to a datetime data type resulted in an > > out-of-range datetime value." > > > > It appears that I need to convert "tblMain.dtOpen" into datetime format to > > calculate DATEDIFF, correct? > > > > How can I convert Char to Datetime? > > > > Thanks in advance. > > Justin > > > > > . > |