Hi All,
After digging out some information on using the CONVERT and CAST
functions to convert a string to a date, I am still having a problem
that I do not understand?
In an attempt to convert a Legacy format for a date e.g. '19960905'
imported into MS SQL Server into a correctly sequenced and delimetered
data '05/09/1996' I am using the following statement for two columns in
my table. Both receive the following exception messages
Column Name = 'DATE_ORIGINAL'
SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
datetime), 103)
MS SQL Server throws the following exception:
'Invalid column name 'DATE_ORIGINAL'
Column Name = 'DATE_RESCHEDULED'
SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
datetime), 103)
MS SQL Server throws the following exception:
'Invalid column name 'DATE_RESCHEDULED'
I have triple checked both column names and they are both correct. Does
anyone know why these errors occur when I try to run this statement?
Does the use of the word DATE have something to do with it perhaps?
Many Thanks
rohan
If these are the entire select statements, you are
forgetting to include the FROM table name - such as
SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8))
AS datetime), 103)
FROM YourTable
-Sue
On 25 Sep 2006 02:18:14 -0700, "red vertigo"
<rohankinsella@.hotmail.com> wrote:
>Hi All,
>After digging out some information on using the CONVERT and CAST
>functions to convert a string to a date, I am still having a problem
>that I do not understand?
>In an attempt to convert a Legacy format for a date e.g. '19960905'
>imported into MS SQL Server into a correctly sequenced and delimetered
>data '05/09/1996' I am using the following statement for two columns in
>my table. Both receive the following exception messages
>Column Name = 'DATE_ORIGINAL'
>SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
>datetime), 103)
>MS SQL Server throws the following exception:
>'Invalid column name 'DATE_ORIGINAL'
>Column Name = 'DATE_RESCHEDULED'
>SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
>datetime), 103)
>MS SQL Server throws the following exception:
>'Invalid column name 'DATE_RESCHEDULED'
>I have triple checked both column names and they are both correct. Does
>anyone know why these errors occur when I try to run this statement?
>Does the use of the word DATE have something to do with it perhaps?
>Many Thanks
>rohan
|||Hi Sue,
Thankyou very much indeed for replying to my post.
Can't believe I left that part off my statement (lol).
This statement works correctly now.
Your help is greatly appreciated.
rohan
Sue Hoegemeier wrote:[vbcol=seagreen]
> If these are the entire select statements, you are
> forgetting to include the FROM table name - such as
> SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8))
> AS datetime), 103)
> FROM YourTable
> -Sue
> On 25 Sep 2006 02:18:14 -0700, "red vertigo"
> <rohankinsella@.hotmail.com> wrote:
|||Hi Rohan
Please note that '19960905' is not in a legacy format. It is actually a
recommended format when working with date data in SQL Server because it is
completely unambiguous. Your desired format of '05/09/1996' could hardly be
called 'correctly sequenced and delimetered (?)' for everyone, since
different people use different delimiters, like dashes or dots, and it is
ambiguous depending on the local. In the US, '05/09/1996' is interpreted as
May 9th. So if '05/09/1996' is what your users are hoping for, that is
fine and at some point in the client app you'll probably want to give it to
them. But just be careful.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"red vertigo" <rohankinsella@.hotmail.com> wrote in message
news:1159175894.377984.228190@.h48g2000cwc.googlegr oups.com...
> Hi All,
> After digging out some information on using the CONVERT and CAST
> functions to convert a string to a date, I am still having a problem
> that I do not understand?
> In an attempt to convert a Legacy format for a date e.g. '19960905'
> imported into MS SQL Server into a correctly sequenced and delimetered
> data '05/09/1996' I am using the following statement for two columns in
> my table. Both receive the following exception messages
> Column Name = 'DATE_ORIGINAL'
> SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
> datetime), 103)
> MS SQL Server throws the following exception:
> 'Invalid column name 'DATE_ORIGINAL'
> Column Name = 'DATE_RESCHEDULED'
> SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
> datetime), 103)
> MS SQL Server throws the following exception:
> 'Invalid column name 'DATE_RESCHEDULED'
> I have triple checked both column names and they are both correct. Does
> anyone know why these errors occur when I try to run this statement?
> Does the use of the word DATE have something to do with it perhaps?
> Many Thanks
> rohan
>
|||Hi Karen,
Interesting. Thanks for the information.
rohan
Kalen Delaney wrote:
[vbcol=seagreen]
> Hi Rohan
> Please note that '19960905' is not in a legacy format. It is actually a
> recommended format when working with date data in SQL Server because it is
> completely unambiguous. Your desired format of '05/09/1996' could hardly be
> called 'correctly sequenced and delimetered (?)' for everyone, since
> different people use different delimiters, like dashes or dots, and it is
> ambiguous depending on the local. In the US, '05/09/1996' is interpreted as
> May 9th. So if '05/09/1996' is what your users are hoping for, that is
> fine and at some point in the client app you'll probably want to give it to
> them. But just be careful.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "red vertigo" <rohankinsella@.hotmail.com> wrote in message
> news:1159175894.377984.228190@.h48g2000cwc.googlegr oups.com...
Showing posts with label invalid. Show all posts
Showing posts with label invalid. Show all posts
Thursday, March 8, 2012
CONVERT statement - Invalid Column Name
Hi All,
After digging out some information on using the CONVERT and CAST
functions to convert a string to a date, I am still having a problem
that I do not understand?
In an attempt to convert a Legacy format for a date e.g. '19960905'
imported into MS SQL Server into a correctly sequenced and delimetered
data '05/09/1996' I am using the following statement for two columns in
my table. Both receive the following exception messages
Column Name = 'DATE_ORIGINAL'
SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
datetime), 103)
MS SQL Server throws the following exception:
'Invalid column name 'DATE_ORIGINAL'
Column Name = 'DATE_RESCHEDULED'
SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
datetime), 103)
MS SQL Server throws the following exception:
'Invalid column name 'DATE_RESCHEDULED'
I have triple checked both column names and they are both correct. Does
anyone know why these errors occur when I try to run this statement?
Does the use of the word DATE have something to do with it perhaps?
Many Thanks
rohanIf these are the entire select statements, you are
forgetting to include the FROM table name - such as
SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8))
AS datetime), 103)
FROM YourTable
-Sue
On 25 Sep 2006 02:18:14 -0700, "red vertigo"
<rohankinsella@.hotmail.com> wrote:
>Hi All,
>After digging out some information on using the CONVERT and CAST
>functions to convert a string to a date, I am still having a problem
>that I do not understand?
>In an attempt to convert a Legacy format for a date e.g. '19960905'
>imported into MS SQL Server into a correctly sequenced and delimetered
>data '05/09/1996' I am using the following statement for two columns in
>my table. Both receive the following exception messages
>Column Name = 'DATE_ORIGINAL'
>SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
>datetime), 103)
>MS SQL Server throws the following exception:
>'Invalid column name 'DATE_ORIGINAL'
>Column Name = 'DATE_RESCHEDULED'
>SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
>datetime), 103)
>MS SQL Server throws the following exception:
>'Invalid column name 'DATE_RESCHEDULED'
>I have triple checked both column names and they are both correct. Does
>anyone know why these errors occur when I try to run this statement?
>Does the use of the word DATE have something to do with it perhaps?
>Many Thanks
>rohan|||Hi Sue,
Thankyou very much indeed for replying to my post.
Can't believe I left that part off my statement (lol).
This statement works correctly now.
Your help is greatly appreciated.
rohan
Sue Hoegemeier wrote:
> If these are the entire select statements, you are
> forgetting to include the FROM table name - such as
> SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8))
> AS datetime), 103)
> FROM YourTable
> -Sue
> On 25 Sep 2006 02:18:14 -0700, "red vertigo"
> <rohankinsella@.hotmail.com> wrote:
> >Hi All,
> >After digging out some information on using the CONVERT and CAST
> >functions to convert a string to a date, I am still having a problem
> >that I do not understand?
> >
> >In an attempt to convert a Legacy format for a date e.g. '19960905'
> >imported into MS SQL Server into a correctly sequenced and delimetered
> >data '05/09/1996' I am using the following statement for two columns in
> >my table. Both receive the following exception messages
> >
> >Column Name = 'DATE_ORIGINAL'
> >SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
> >datetime), 103)
> >
> >MS SQL Server throws the following exception:
> >
> >'Invalid column name 'DATE_ORIGINAL'
> >
> >Column Name = 'DATE_RESCHEDULED'
> >SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
> >datetime), 103)
> >
> >MS SQL Server throws the following exception:
> >
> >'Invalid column name 'DATE_RESCHEDULED'
> >
> >I have triple checked both column names and they are both correct. Does
> >anyone know why these errors occur when I try to run this statement?
> >
> >Does the use of the word DATE have something to do with it perhaps?
> >
> >Many Thanks
> >
> >rohan|||Hi Rohan
Please note that '19960905' is not in a legacy format. It is actually a
recommended format when working with date data in SQL Server because it is
completely unambiguous. Your desired format of '05/09/1996' could hardly be
called 'correctly sequenced and delimetered (')' for everyone, since
different people use different delimiters, like dashes or dots, and it is
ambiguous depending on the local. In the US, '05/09/1996' is interpreted as
May 9th. So if '05/09/1996' is what your users are hoping for, that is
fine and at some point in the client app you'll probably want to give it to
them. But just be careful.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"red vertigo" <rohankinsella@.hotmail.com> wrote in message
news:1159175894.377984.228190@.h48g2000cwc.googlegroups.com...
> Hi All,
> After digging out some information on using the CONVERT and CAST
> functions to convert a string to a date, I am still having a problem
> that I do not understand?
> In an attempt to convert a Legacy format for a date e.g. '19960905'
> imported into MS SQL Server into a correctly sequenced and delimetered
> data '05/09/1996' I am using the following statement for two columns in
> my table. Both receive the following exception messages
> Column Name = 'DATE_ORIGINAL'
> SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
> datetime), 103)
> MS SQL Server throws the following exception:
> 'Invalid column name 'DATE_ORIGINAL'
> Column Name = 'DATE_RESCHEDULED'
> SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
> datetime), 103)
> MS SQL Server throws the following exception:
> 'Invalid column name 'DATE_RESCHEDULED'
> I have triple checked both column names and they are both correct. Does
> anyone know why these errors occur when I try to run this statement?
> Does the use of the word DATE have something to do with it perhaps?
> Many Thanks
> rohan
>|||Hi Karen,
Interesting. Thanks for the information.
rohan
Kalen Delaney wrote:
> Hi Rohan
> Please note that '19960905' is not in a legacy format. It is actually a
> recommended format when working with date data in SQL Server because it is
> completely unambiguous. Your desired format of '05/09/1996' could hardly be
> called 'correctly sequenced and delimetered (')' for everyone, since
> different people use different delimiters, like dashes or dots, and it is
> ambiguous depending on the local. In the US, '05/09/1996' is interpreted as
> May 9th. So if '05/09/1996' is what your users are hoping for, that is
> fine and at some point in the client app you'll probably want to give it to
> them. But just be careful.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "red vertigo" <rohankinsella@.hotmail.com> wrote in message
> news:1159175894.377984.228190@.h48g2000cwc.googlegroups.com...
> > Hi All,
> > After digging out some information on using the CONVERT and CAST
> > functions to convert a string to a date, I am still having a problem
> > that I do not understand?
> >
> > In an attempt to convert a Legacy format for a date e.g. '19960905'
> > imported into MS SQL Server into a correctly sequenced and delimetered
> > data '05/09/1996' I am using the following statement for two columns in
> > my table. Both receive the following exception messages
> >
> > Column Name = 'DATE_ORIGINAL'
> > SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
> > datetime), 103)
> >
> > MS SQL Server throws the following exception:
> >
> > 'Invalid column name 'DATE_ORIGINAL'
> >
> > Column Name = 'DATE_RESCHEDULED'
> > SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
> > datetime), 103)
> >
> > MS SQL Server throws the following exception:
> >
> > 'Invalid column name 'DATE_RESCHEDULED'
> >
> > I have triple checked both column names and they are both correct. Does
> > anyone know why these errors occur when I try to run this statement?
> >
> > Does the use of the word DATE have something to do with it perhaps?
> >
> > Many Thanks
> >
> > rohan
> >
After digging out some information on using the CONVERT and CAST
functions to convert a string to a date, I am still having a problem
that I do not understand?
In an attempt to convert a Legacy format for a date e.g. '19960905'
imported into MS SQL Server into a correctly sequenced and delimetered
data '05/09/1996' I am using the following statement for two columns in
my table. Both receive the following exception messages
Column Name = 'DATE_ORIGINAL'
SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
datetime), 103)
MS SQL Server throws the following exception:
'Invalid column name 'DATE_ORIGINAL'
Column Name = 'DATE_RESCHEDULED'
SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
datetime), 103)
MS SQL Server throws the following exception:
'Invalid column name 'DATE_RESCHEDULED'
I have triple checked both column names and they are both correct. Does
anyone know why these errors occur when I try to run this statement?
Does the use of the word DATE have something to do with it perhaps?
Many Thanks
rohanIf these are the entire select statements, you are
forgetting to include the FROM table name - such as
SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8))
AS datetime), 103)
FROM YourTable
-Sue
On 25 Sep 2006 02:18:14 -0700, "red vertigo"
<rohankinsella@.hotmail.com> wrote:
>Hi All,
>After digging out some information on using the CONVERT and CAST
>functions to convert a string to a date, I am still having a problem
>that I do not understand?
>In an attempt to convert a Legacy format for a date e.g. '19960905'
>imported into MS SQL Server into a correctly sequenced and delimetered
>data '05/09/1996' I am using the following statement for two columns in
>my table. Both receive the following exception messages
>Column Name = 'DATE_ORIGINAL'
>SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
>datetime), 103)
>MS SQL Server throws the following exception:
>'Invalid column name 'DATE_ORIGINAL'
>Column Name = 'DATE_RESCHEDULED'
>SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
>datetime), 103)
>MS SQL Server throws the following exception:
>'Invalid column name 'DATE_RESCHEDULED'
>I have triple checked both column names and they are both correct. Does
>anyone know why these errors occur when I try to run this statement?
>Does the use of the word DATE have something to do with it perhaps?
>Many Thanks
>rohan|||Hi Sue,
Thankyou very much indeed for replying to my post.
Can't believe I left that part off my statement (lol).
This statement works correctly now.
Your help is greatly appreciated.
rohan
Sue Hoegemeier wrote:
> If these are the entire select statements, you are
> forgetting to include the FROM table name - such as
> SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8))
> AS datetime), 103)
> FROM YourTable
> -Sue
> On 25 Sep 2006 02:18:14 -0700, "red vertigo"
> <rohankinsella@.hotmail.com> wrote:
> >Hi All,
> >After digging out some information on using the CONVERT and CAST
> >functions to convert a string to a date, I am still having a problem
> >that I do not understand?
> >
> >In an attempt to convert a Legacy format for a date e.g. '19960905'
> >imported into MS SQL Server into a correctly sequenced and delimetered
> >data '05/09/1996' I am using the following statement for two columns in
> >my table. Both receive the following exception messages
> >
> >Column Name = 'DATE_ORIGINAL'
> >SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
> >datetime), 103)
> >
> >MS SQL Server throws the following exception:
> >
> >'Invalid column name 'DATE_ORIGINAL'
> >
> >Column Name = 'DATE_RESCHEDULED'
> >SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
> >datetime), 103)
> >
> >MS SQL Server throws the following exception:
> >
> >'Invalid column name 'DATE_RESCHEDULED'
> >
> >I have triple checked both column names and they are both correct. Does
> >anyone know why these errors occur when I try to run this statement?
> >
> >Does the use of the word DATE have something to do with it perhaps?
> >
> >Many Thanks
> >
> >rohan|||Hi Rohan
Please note that '19960905' is not in a legacy format. It is actually a
recommended format when working with date data in SQL Server because it is
completely unambiguous. Your desired format of '05/09/1996' could hardly be
called 'correctly sequenced and delimetered (')' for everyone, since
different people use different delimiters, like dashes or dots, and it is
ambiguous depending on the local. In the US, '05/09/1996' is interpreted as
May 9th. So if '05/09/1996' is what your users are hoping for, that is
fine and at some point in the client app you'll probably want to give it to
them. But just be careful.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"red vertigo" <rohankinsella@.hotmail.com> wrote in message
news:1159175894.377984.228190@.h48g2000cwc.googlegroups.com...
> Hi All,
> After digging out some information on using the CONVERT and CAST
> functions to convert a string to a date, I am still having a problem
> that I do not understand?
> In an attempt to convert a Legacy format for a date e.g. '19960905'
> imported into MS SQL Server into a correctly sequenced and delimetered
> data '05/09/1996' I am using the following statement for two columns in
> my table. Both receive the following exception messages
> Column Name = 'DATE_ORIGINAL'
> SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
> datetime), 103)
> MS SQL Server throws the following exception:
> 'Invalid column name 'DATE_ORIGINAL'
> Column Name = 'DATE_RESCHEDULED'
> SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
> datetime), 103)
> MS SQL Server throws the following exception:
> 'Invalid column name 'DATE_RESCHEDULED'
> I have triple checked both column names and they are both correct. Does
> anyone know why these errors occur when I try to run this statement?
> Does the use of the word DATE have something to do with it perhaps?
> Many Thanks
> rohan
>|||Hi Karen,
Interesting. Thanks for the information.
rohan
Kalen Delaney wrote:
> Hi Rohan
> Please note that '19960905' is not in a legacy format. It is actually a
> recommended format when working with date data in SQL Server because it is
> completely unambiguous. Your desired format of '05/09/1996' could hardly be
> called 'correctly sequenced and delimetered (')' for everyone, since
> different people use different delimiters, like dashes or dots, and it is
> ambiguous depending on the local. In the US, '05/09/1996' is interpreted as
> May 9th. So if '05/09/1996' is what your users are hoping for, that is
> fine and at some point in the client app you'll probably want to give it to
> them. But just be careful.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "red vertigo" <rohankinsella@.hotmail.com> wrote in message
> news:1159175894.377984.228190@.h48g2000cwc.googlegroups.com...
> > Hi All,
> > After digging out some information on using the CONVERT and CAST
> > functions to convert a string to a date, I am still having a problem
> > that I do not understand?
> >
> > In an attempt to convert a Legacy format for a date e.g. '19960905'
> > imported into MS SQL Server into a correctly sequenced and delimetered
> > data '05/09/1996' I am using the following statement for two columns in
> > my table. Both receive the following exception messages
> >
> > Column Name = 'DATE_ORIGINAL'
> > SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
> > datetime), 103)
> >
> > MS SQL Server throws the following exception:
> >
> > 'Invalid column name 'DATE_ORIGINAL'
> >
> > Column Name = 'DATE_RESCHEDULED'
> > SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
> > datetime), 103)
> >
> > MS SQL Server throws the following exception:
> >
> > 'Invalid column name 'DATE_RESCHEDULED'
> >
> > I have triple checked both column names and they are both correct. Does
> > anyone know why these errors occur when I try to run this statement?
> >
> > Does the use of the word DATE have something to do with it perhaps?
> >
> > Many Thanks
> >
> > rohan
> >
CONVERT statement - Invalid Column Name
Hi All,
After digging out some information on using the CONVERT and CAST
functions to convert a string to a date, I am still having a problem
that I do not understand?
In an attempt to convert a Legacy format for a date e.g. '19960905'
imported into MS SQL Server into a correctly sequenced and delimetered
data '05/09/1996' I am using the following statement for two columns in
my table. Both receive the following exception messages
Column Name = 'DATE_ORIGINAL'
SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
datetime), 103)
MS SQL Server throws the following exception:
'Invalid column name 'DATE_ORIGINAL'
Column Name = 'DATE_RESCHEDULED'
SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
datetime), 103)
MS SQL Server throws the following exception:
'Invalid column name 'DATE_RESCHEDULED'
I have triple checked both column names and they are both correct. Does
anyone know why these errors occur when I try to run this statement?
Does the use of the word DATE have something to do with it perhaps?
Many Thanks
rohanIf these are the entire select statements, you are
forgetting to include the FROM table name - such as
SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8))
AS datetime), 103)
FROM YourTable
-Sue
On 25 Sep 2006 02:18:14 -0700, "red vertigo"
<rohankinsella@.hotmail.com> wrote:
>Hi All,
>After digging out some information on using the CONVERT and CAST
>functions to convert a string to a date, I am still having a problem
>that I do not understand?
>In an attempt to convert a Legacy format for a date e.g. '19960905'
>imported into MS SQL Server into a correctly sequenced and delimetered
>data '05/09/1996' I am using the following statement for two columns in
>my table. Both receive the following exception messages
>Column Name = 'DATE_ORIGINAL'
>SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
>datetime), 103)
>MS SQL Server throws the following exception:
>'Invalid column name 'DATE_ORIGINAL'
>Column Name = 'DATE_RESCHEDULED'
>SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
>datetime), 103)
>MS SQL Server throws the following exception:
>'Invalid column name 'DATE_RESCHEDULED'
>I have triple checked both column names and they are both correct. Does
>anyone know why these errors occur when I try to run this statement?
>Does the use of the word DATE have something to do with it perhaps?
>Many Thanks
>rohan|||Hi Sue,
Thankyou very much indeed for replying to my post.
Can't believe I left that part off my statement (lol).
This statement works correctly now.
Your help is greatly appreciated.
rohan
Sue Hoegemeier wrote:[vbcol=seagreen]
> If these are the entire select statements, you are
> forgetting to include the FROM table name - such as
> SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8))
> AS datetime), 103)
> FROM YourTable
> -Sue
> On 25 Sep 2006 02:18:14 -0700, "red vertigo"
> <rohankinsella@.hotmail.com> wrote:
>|||Hi Rohan
Please note that '19960905' is not in a legacy format. It is actually a
recommended format when working with date data in SQL Server because it is
completely unambiguous. Your desired format of '05/09/1996' could hardly be
called 'correctly sequenced and delimetered (')' for everyone, since
different people use different delimiters, like dashes or dots, and it is
ambiguous depending on the local. In the US, '05/09/1996' is interpreted as
May 9th. So if '05/09/1996' is what your users are hoping for, that is
fine and at some point in the client app you'll probably want to give it to
them. But just be careful.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"red vertigo" <rohankinsella@.hotmail.com> wrote in message
news:1159175894.377984.228190@.h48g2000cwc.googlegroups.com...
> Hi All,
> After digging out some information on using the CONVERT and CAST
> functions to convert a string to a date, I am still having a problem
> that I do not understand?
> In an attempt to convert a Legacy format for a date e.g. '19960905'
> imported into MS SQL Server into a correctly sequenced and delimetered
> data '05/09/1996' I am using the following statement for two columns in
> my table. Both receive the following exception messages
> Column Name = 'DATE_ORIGINAL'
> SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
> datetime), 103)
> MS SQL Server throws the following exception:
> 'Invalid column name 'DATE_ORIGINAL'
> Column Name = 'DATE_RESCHEDULED'
> SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
> datetime), 103)
> MS SQL Server throws the following exception:
> 'Invalid column name 'DATE_RESCHEDULED'
> I have triple checked both column names and they are both correct. Does
> anyone know why these errors occur when I try to run this statement?
> Does the use of the word DATE have something to do with it perhaps?
> Many Thanks
> rohan
>|||Hi Karen,
Interesting. Thanks for the information.
rohan
Kalen Delaney wrote:
[vbcol=seagreen]
> Hi Rohan
> Please note that '19960905' is not in a legacy format. It is actually a
> recommended format when working with date data in SQL Server because it is
> completely unambiguous. Your desired format of '05/09/1996' could hardly
be
> called 'correctly sequenced and delimetered (')' for everyone, since
> different people use different delimiters, like dashes or dots, and it is
> ambiguous depending on the local. In the US, '05/09/1996' is interpreted
as
> May 9th. So if '05/09/1996' is what your users are hoping for, that is
> fine and at some point in the client app you'll probably want to give it t
o
> them. But just be careful.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "red vertigo" <rohankinsella@.hotmail.com> wrote in message
> news:1159175894.377984.228190@.h48g2000cwc.googlegroups.com...
After digging out some information on using the CONVERT and CAST
functions to convert a string to a date, I am still having a problem
that I do not understand?
In an attempt to convert a Legacy format for a date e.g. '19960905'
imported into MS SQL Server into a correctly sequenced and delimetered
data '05/09/1996' I am using the following statement for two columns in
my table. Both receive the following exception messages
Column Name = 'DATE_ORIGINAL'
SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
datetime), 103)
MS SQL Server throws the following exception:
'Invalid column name 'DATE_ORIGINAL'
Column Name = 'DATE_RESCHEDULED'
SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
datetime), 103)
MS SQL Server throws the following exception:
'Invalid column name 'DATE_RESCHEDULED'
I have triple checked both column names and they are both correct. Does
anyone know why these errors occur when I try to run this statement?
Does the use of the word DATE have something to do with it perhaps?
Many Thanks
rohanIf these are the entire select statements, you are
forgetting to include the FROM table name - such as
SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8))
AS datetime), 103)
FROM YourTable
-Sue
On 25 Sep 2006 02:18:14 -0700, "red vertigo"
<rohankinsella@.hotmail.com> wrote:
>Hi All,
>After digging out some information on using the CONVERT and CAST
>functions to convert a string to a date, I am still having a problem
>that I do not understand?
>In an attempt to convert a Legacy format for a date e.g. '19960905'
>imported into MS SQL Server into a correctly sequenced and delimetered
>data '05/09/1996' I am using the following statement for two columns in
>my table. Both receive the following exception messages
>Column Name = 'DATE_ORIGINAL'
>SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
>datetime), 103)
>MS SQL Server throws the following exception:
>'Invalid column name 'DATE_ORIGINAL'
>Column Name = 'DATE_RESCHEDULED'
>SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
>datetime), 103)
>MS SQL Server throws the following exception:
>'Invalid column name 'DATE_RESCHEDULED'
>I have triple checked both column names and they are both correct. Does
>anyone know why these errors occur when I try to run this statement?
>Does the use of the word DATE have something to do with it perhaps?
>Many Thanks
>rohan|||Hi Sue,
Thankyou very much indeed for replying to my post.
Can't believe I left that part off my statement (lol).
This statement works correctly now.
Your help is greatly appreciated.
rohan
Sue Hoegemeier wrote:[vbcol=seagreen]
> If these are the entire select statements, you are
> forgetting to include the FROM table name - such as
> SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8))
> AS datetime), 103)
> FROM YourTable
> -Sue
> On 25 Sep 2006 02:18:14 -0700, "red vertigo"
> <rohankinsella@.hotmail.com> wrote:
>|||Hi Rohan
Please note that '19960905' is not in a legacy format. It is actually a
recommended format when working with date data in SQL Server because it is
completely unambiguous. Your desired format of '05/09/1996' could hardly be
called 'correctly sequenced and delimetered (')' for everyone, since
different people use different delimiters, like dashes or dots, and it is
ambiguous depending on the local. In the US, '05/09/1996' is interpreted as
May 9th. So if '05/09/1996' is what your users are hoping for, that is
fine and at some point in the client app you'll probably want to give it to
them. But just be careful.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"red vertigo" <rohankinsella@.hotmail.com> wrote in message
news:1159175894.377984.228190@.h48g2000cwc.googlegroups.com...
> Hi All,
> After digging out some information on using the CONVERT and CAST
> functions to convert a string to a date, I am still having a problem
> that I do not understand?
> In an attempt to convert a Legacy format for a date e.g. '19960905'
> imported into MS SQL Server into a correctly sequenced and delimetered
> data '05/09/1996' I am using the following statement for two columns in
> my table. Both receive the following exception messages
> Column Name = 'DATE_ORIGINAL'
> SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
> datetime), 103)
> MS SQL Server throws the following exception:
> 'Invalid column name 'DATE_ORIGINAL'
> Column Name = 'DATE_RESCHEDULED'
> SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
> datetime), 103)
> MS SQL Server throws the following exception:
> 'Invalid column name 'DATE_RESCHEDULED'
> I have triple checked both column names and they are both correct. Does
> anyone know why these errors occur when I try to run this statement?
> Does the use of the word DATE have something to do with it perhaps?
> Many Thanks
> rohan
>|||Hi Karen,
Interesting. Thanks for the information.
rohan
Kalen Delaney wrote:
[vbcol=seagreen]
> Hi Rohan
> Please note that '19960905' is not in a legacy format. It is actually a
> recommended format when working with date data in SQL Server because it is
> completely unambiguous. Your desired format of '05/09/1996' could hardly
be
> called 'correctly sequenced and delimetered (')' for everyone, since
> different people use different delimiters, like dashes or dots, and it is
> ambiguous depending on the local. In the US, '05/09/1996' is interpreted
as
> May 9th. So if '05/09/1996' is what your users are hoping for, that is
> fine and at some point in the client app you'll probably want to give it t
o
> them. But just be careful.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "red vertigo" <rohankinsella@.hotmail.com> wrote in message
> news:1159175894.377984.228190@.h48g2000cwc.googlegroups.com...
Subscribe to:
Posts (Atom)