Help on design table!

Hi,
I'm using SQL 2000. I have table called invoice which has 2 cols "invoice"
(var 10) / "invoicedate" (var 25).
The question will rise up that why I defined col "invoicedate" as var.
That's my mistake when I was new to sql table design. At the front end of
the application, user scans the invoices into this table without seeing
'invoicedate' field. However I like to keep date info in this column, so I
set default value in the table getdate(). By mistake, I defined the type as
var instead of date. Now I'm getting into problem. There are tons of records
in this table. I like to query the table by date which it's so hard.
I like to know at this point, can I change the type to date? Would it cause
to lose the data? Or if you have better idea that would be great help.
I hope I explained the problem properly, if you have any questions, please
feel free let me know.
Thanks,
SarahYou can change the datatype and you should not lose any data values.
However, if any of the rows contain values that would not convert to a
legitimate date, the conversion will fail. You will then have to find the
non-conforming rows and correct them.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Sarah" <sguo@.coopervision.com> wrote in message
news:eSeat6nqGHA.2232@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm using SQL 2000. I have table called invoice which has 2 cols "invoice"
> (var 10) / "invoicedate" (var 25).
> The question will rise up that why I defined col "invoicedate" as var.
> That's my mistake when I was new to sql table design. At the front end of
> the application, user scans the invoices into this table without seeing
> 'invoicedate' field. However I like to keep date info in this column, so I
> set default value in the table getdate(). By mistake, I defined the type
> as var instead of date. Now I'm getting into problem. There are tons of
> records in this table. I like to query the table by date which it's so
> hard.
> I like to know at this point, can I change the type to date? Would it
> cause to lose the data? Or if you have better idea that would be great
> help.
> I hope I explained the problem properly, if you have any questions, please
> feel free let me know.
> Thanks,
> Sarah
>|||Do not forget to backup your data before changing to date datatype!|||Another suggestion would be to create a new column and give it a data
type of DateTime. Then run an update statement to copy the data from
var column to the new column.
IF the copy process finishes without error...delete old column.
Whatever you do, make sure you backup your system to a test environment
and convert it there before converting your production machine. Also be
sure to verify the dates for accuracy.
That's why I like my 2 column method, you'll be able to verify the data
before dropping the original column.
In the future it would be helpful if you posted sample data and gave
some specifics like how many rows this table contains.
Sarah wrote:
> Hi,
> I'm using SQL 2000. I have table called invoice which has 2 cols "invoice"
> (var 10) / "invoicedate" (var 25).
> The question will rise up that why I defined col "invoicedate" as var.
> That's my mistake when I was new to sql table design. At the front end of
> the application, user scans the invoices into this table without seeing
> 'invoicedate' field. However I like to keep date info in this column, so I
> set default value in the table getdate(). By mistake, I defined the type as
> var instead of date. Now I'm getting into problem. There are tons of records
> in this table. I like to query the table by date which it's so hard.
> I like to know at this point, can I change the type to date? Would it cause
> to lose the data? Or if you have better idea that would be great help.
> I hope I explained the problem properly, if you have any questions, please
> feel free let me know.
> Thanks,
> Sarah|||What is the format in the "invoicedate" column? ie mmddyyy or ddmmyyyy
etc.
Sarah wrote:
> Hi,
> I'm using SQL 2000. I have table called invoice which has 2 cols "invoice"
> (var 10) / "invoicedate" (var 25).
> The question will rise up that why I defined col "invoicedate" as var.
> That's my mistake when I was new to sql table design. At the front end of
> the application, user scans the invoices into this table without seeing
> 'invoicedate' field. However I like to keep date info in this column, so I
> set default value in the table getdate(). By mistake, I defined the type as
> var instead of date. Now I'm getting into problem. There are tons of records
> in this table. I like to query the table by date which it's so hard.
> I like to know at this point, can I change the type to date? Would it cause
> to lose the data? Or if you have better idea that would be great help.
> I hope I explained the problem properly, if you have any questions, please
> feel free let me know.
> Thanks,
> Sarah|||Hi All,
Thanks so much for all the responses. I will take Izzy's idea to try. Do it
in test system to see. Also the data which has been in the column is like
"mmm dd yyyy hh:mm:ss am or pm". How would I convert old data into datatime
type in the new column? I like to keep old and new data in the same format
like "mmm dd yyyy" or "mm/dd/yy". Is it possible? If you can show me the
function that would be great.
Thanks again,
Sarah
"Izzy" <israel.richner@.gmail.com> wrote in message
news:1153241956.118537.211970@.h48g2000cwc.googlegroups.com...
> Another suggestion would be to create a new column and give it a data
> type of DateTime. Then run an update statement to copy the data from
> var column to the new column.
> IF the copy process finishes without error...delete old column.
> Whatever you do, make sure you backup your system to a test environment
> and convert it there before converting your production machine. Also be
> sure to verify the dates for accuracy.
> That's why I like my 2 column method, you'll be able to verify the data
> before dropping the original column.
> In the future it would be helpful if you posted sample data and gave
> some specifics like how many rows this table contains.
>
> Sarah wrote:
>> Hi,
>> I'm using SQL 2000. I have table called invoice which has 2 cols
>> "invoice"
>> (var 10) / "invoicedate" (var 25).
>> The question will rise up that why I defined col "invoicedate" as var.
>> That's my mistake when I was new to sql table design. At the front end of
>> the application, user scans the invoices into this table without seeing
>> 'invoicedate' field. However I like to keep date info in this column, so
>> I
>> set default value in the table getdate(). By mistake, I defined the type
>> as
>> var instead of date. Now I'm getting into problem. There are tons of
>> records
>> in this table. I like to query the table by date which it's so hard.
>> I like to know at this point, can I change the type to date? Would it
>> cause
>> to lose the data? Or if you have better idea that would be great help.
>> I hope I explained the problem properly, if you have any questions,
>> please
>> feel free let me know.
>> Thanks,
>> Sarah
>|||Hi,
So after you create the new column in Invoice table, to convert varchar
to datetime datatype, you can do something like this:
/* Create a table to store Varchar Invoice dates datatype */
Create Table #Temp (
InvoinceDate Varchar(25)
)
/* Insert Varchar Invoice dates into temp table
from Invoice table */
Insert #Temp (
InvoinceDate
)
Select InvoiceDate From Invoice
/* Insert into new InvoiceDate column with DateTime datatype in
Invoice after converting varchar to datetime datatype */
Insert Invoice (
NewInvoiceDate
)
Select Convert(DateTime, InvoiceDate, 1) As NewInvoiceDate
>From #Temp
/* Drop temp table */
Drop Table #Temp
Cheers!
- Lem
Sarah wrote:
> Hi All,
> Thanks so much for all the responses. I will take Izzy's idea to try. Do it
> in test system to see. Also the data which has been in the column is like
> "mmm dd yyyy hh:mm:ss am or pm". How would I convert old data into datatime
> type in the new column? I like to keep old and new data in the same format
> like "mmm dd yyyy" or "mm/dd/yy". Is it possible? If you can show me the
> function that would be great.
> Thanks again,
> Sarah
> "Izzy" <israel.richner@.gmail.com> wrote in message
> news:1153241956.118537.211970@.h48g2000cwc.googlegroups.com...
> > Another suggestion would be to create a new column and give it a data
> > type of DateTime. Then run an update statement to copy the data from
> > var column to the new column.
> >
> > IF the copy process finishes without error...delete old column.
> >
> > Whatever you do, make sure you backup your system to a test environment
> > and convert it there before converting your production machine. Also be
> > sure to verify the dates for accuracy.
> >
> > That's why I like my 2 column method, you'll be able to verify the data
> > before dropping the original column.
> >
> > In the future it would be helpful if you posted sample data and gave
> > some specifics like how many rows this table contains.
> >
> >
> > Sarah wrote:
> >> Hi,
> >>
> >> I'm using SQL 2000. I have table called invoice which has 2 cols
> >> "invoice"
> >> (var 10) / "invoicedate" (var 25).
> >> The question will rise up that why I defined col "invoicedate" as var.
> >> That's my mistake when I was new to sql table design. At the front end of
> >> the application, user scans the invoices into this table without seeing
> >> 'invoicedate' field. However I like to keep date info in this column, so
> >> I
> >> set default value in the table getdate(). By mistake, I defined the type
> >> as
> >> var instead of date. Now I'm getting into problem. There are tons of
> >> records
> >> in this table. I like to query the table by date which it's so hard.
> >> I like to know at this point, can I change the type to date? Would it
> >> cause
> >> to lose the data? Or if you have better idea that would be great help.
> >> I hope I explained the problem properly, if you have any questions,
> >> please
> >> feel free let me know.
> >>
> >> Thanks,
> >> Sarah
> >|||Hello Lem,
Thanks for the detail. I will do this to see. Thanks a lot
<lgalumbres@.gmail.com> wrote in message
news:1153253362.404116.316160@.b28g2000cwb.googlegroups.com...
> Hi,
> So after you create the new column in Invoice table, to convert varchar
> to datetime datatype, you can do something like this:
> /* Create a table to store Varchar Invoice dates datatype */
> Create Table #Temp (
> InvoinceDate Varchar(25)
> )
> /* Insert Varchar Invoice dates into temp table
> from Invoice table */
> Insert #Temp (
> InvoinceDate
> )
> Select InvoiceDate From Invoice
> /* Insert into new InvoiceDate column with DateTime datatype in
> Invoice after converting varchar to datetime datatype */
> Insert Invoice (
> NewInvoiceDate
> )
> Select Convert(DateTime, InvoiceDate, 1) As NewInvoiceDate
>>From #Temp
> /* Drop temp table */
> Drop Table #Temp
> Cheers!
> - Lem
> Sarah wrote:
>> Hi All,
>> Thanks so much for all the responses. I will take Izzy's idea to try. Do
>> it
>> in test system to see. Also the data which has been in the column is like
>> "mmm dd yyyy hh:mm:ss am or pm". How would I convert old data into
>> datatime
>> type in the new column? I like to keep old and new data in the same
>> format
>> like "mmm dd yyyy" or "mm/dd/yy". Is it possible? If you can show me the
>> function that would be great.
>> Thanks again,
>> Sarah
>> "Izzy" <israel.richner@.gmail.com> wrote in message
>> news:1153241956.118537.211970@.h48g2000cwc.googlegroups.com...
>> > Another suggestion would be to create a new column and give it a data
>> > type of DateTime. Then run an update statement to copy the data from
>> > var column to the new column.
>> >
>> > IF the copy process finishes without error...delete old column.
>> >
>> > Whatever you do, make sure you backup your system to a test environment
>> > and convert it there before converting your production machine. Also be
>> > sure to verify the dates for accuracy.
>> >
>> > That's why I like my 2 column method, you'll be able to verify the data
>> > before dropping the original column.
>> >
>> > In the future it would be helpful if you posted sample data and gave
>> > some specifics like how many rows this table contains.
>> >
>> >
>> > Sarah wrote:
>> >> Hi,
>> >>
>> >> I'm using SQL 2000. I have table called invoice which has 2 cols
>> >> "invoice"
>> >> (var 10) / "invoicedate" (var 25).
>> >> The question will rise up that why I defined col "invoicedate" as var.
>> >> That's my mistake when I was new to sql table design. At the front end
>> >> of
>> >> the application, user scans the invoices into this table without
>> >> seeing
>> >> 'invoicedate' field. However I like to keep date info in this column,
>> >> so
>> >> I
>> >> set default value in the table getdate(). By mistake, I defined the
>> >> type
>> >> as
>> >> var instead of date. Now I'm getting into problem. There are tons of
>> >> records
>> >> in this table. I like to query the table by date which it's so hard.
>> >> I like to know at this point, can I change the type to date? Would it
>> >> cause
>> >> to lose the data? Or if you have better idea that would be great help.
>> >> I hope I explained the problem properly, if you have any questions,
>> >> please
>> >> feel free let me know.
>> >>
>> >> Thanks,
>> >> Sarah
>> >
>|||Wouldn't it be a lot easier to do it like this
UPDATE INVOICE
SET NEW_INVOICEDATE = Convert(DateTime, InvoiceDate, 1)
That temp table idea looks a little scary, how will it ensure the right
dates get reinserted into the correct rows.
Long as the update completes with no errors AND the data looks the same
then you could drop the old column.
Sarah wrote:
> Hello Lem,
> Thanks for the detail. I will do this to see. Thanks a lot
> <lgalumbres@.gmail.com> wrote in message
> news:1153253362.404116.316160@.b28g2000cwb.googlegroups.com...
> > Hi,
> >
> > So after you create the new column in Invoice table, to convert varchar
> > to datetime datatype, you can do something like this:
> >
> > /* Create a table to store Varchar Invoice dates datatype */
> > Create Table #Temp (
> > InvoinceDate Varchar(25)
> > )
> > /* Insert Varchar Invoice dates into temp table
> > from Invoice table */
> > Insert #Temp (
> > InvoinceDate
> > )
> > Select InvoiceDate From Invoice
> > /* Insert into new InvoiceDate column with DateTime datatype in
> > Invoice after converting varchar to datetime datatype */
> > Insert Invoice (
> > NewInvoiceDate
> > )
> > Select Convert(DateTime, InvoiceDate, 1) As NewInvoiceDate
> >>From #Temp
> >
> > /* Drop temp table */
> > Drop Table #Temp
> >
> > Cheers!
> > - Lem
> >
> > Sarah wrote:
> >> Hi All,
> >>
> >> Thanks so much for all the responses. I will take Izzy's idea to try. Do
> >> it
> >> in test system to see. Also the data which has been in the column is like
> >> "mmm dd yyyy hh:mm:ss am or pm". How would I convert old data into
> >> datatime
> >> type in the new column? I like to keep old and new data in the same
> >> format
> >> like "mmm dd yyyy" or "mm/dd/yy". Is it possible? If you can show me the
> >> function that would be great.
> >>
> >> Thanks again,
> >> Sarah
> >> "Izzy" <israel.richner@.gmail.com> wrote in message
> >> news:1153241956.118537.211970@.h48g2000cwc.googlegroups.com...
> >> > Another suggestion would be to create a new column and give it a data
> >> > type of DateTime. Then run an update statement to copy the data from
> >> > var column to the new column.
> >> >
> >> > IF the copy process finishes without error...delete old column.
> >> >
> >> > Whatever you do, make sure you backup your system to a test environment
> >> > and convert it there before converting your production machine. Also be
> >> > sure to verify the dates for accuracy.
> >> >
> >> > That's why I like my 2 column method, you'll be able to verify the data
> >> > before dropping the original column.
> >> >
> >> > In the future it would be helpful if you posted sample data and gave
> >> > some specifics like how many rows this table contains.
> >> >
> >> >
> >> > Sarah wrote:
> >> >> Hi,
> >> >>
> >> >> I'm using SQL 2000. I have table called invoice which has 2 cols
> >> >> "invoice"
> >> >> (var 10) / "invoicedate" (var 25).
> >> >> The question will rise up that why I defined col "invoicedate" as var.
> >> >> That's my mistake when I was new to sql table design. At the front end
> >> >> of
> >> >> the application, user scans the invoices into this table without
> >> >> seeing
> >> >> 'invoicedate' field. However I like to keep date info in this column,
> >> >> so
> >> >> I
> >> >> set default value in the table getdate(). By mistake, I defined the
> >> >> type
> >> >> as
> >> >> var instead of date. Now I'm getting into problem. There are tons of
> >> >> records
> >> >> in this table. I like to query the table by date which it's so hard.
> >> >> I like to know at this point, can I change the type to date? Would it
> >> >> cause
> >> >> to lose the data? Or if you have better idea that would be great help.
> >> >> I hope I explained the problem properly, if you have any questions,
> >> >> please
> >> >> feel free let me know.
> >> >>
> >> >> Thanks,
> >> >> Sarah
> >> >
> >|||OH...about the date format.
It doesn't matter how it's stored in SQL Server it only matters how you
format and display the date in your client application.
As far as SQL is concerned 2006-07-18 is the same as 7/18/2006.
Izzy wrote:
> Wouldn't it be a lot easier to do it like this
> UPDATE INVOICE
> SET NEW_INVOICEDATE = Convert(DateTime, InvoiceDate, 1)
> That temp table idea looks a little scary, how will it ensure the right
> dates get reinserted into the correct rows.
> Long as the update completes with no errors AND the data looks the same
> then you could drop the old column.
> Sarah wrote:
> > Hello Lem,
> >
> > Thanks for the detail. I will do this to see. Thanks a lot
> > <lgalumbres@.gmail.com> wrote in message
> > news:1153253362.404116.316160@.b28g2000cwb.googlegroups.com...
> > > Hi,
> > >
> > > So after you create the new column in Invoice table, to convert varchar
> > > to datetime datatype, you can do something like this:
> > >
> > > /* Create a table to store Varchar Invoice dates datatype */
> > > Create Table #Temp (
> > > InvoinceDate Varchar(25)
> > > )
> > > /* Insert Varchar Invoice dates into temp table
> > > from Invoice table */
> > > Insert #Temp (
> > > InvoinceDate
> > > )
> > > Select InvoiceDate From Invoice
> > > /* Insert into new InvoiceDate column with DateTime datatype in
> > > Invoice after converting varchar to datetime datatype */
> > > Insert Invoice (
> > > NewInvoiceDate
> > > )
> > > Select Convert(DateTime, InvoiceDate, 1) As NewInvoiceDate
> > >>From #Temp
> > >
> > > /* Drop temp table */
> > > Drop Table #Temp
> > >
> > > Cheers!
> > > - Lem
> > >
> > > Sarah wrote:
> > >> Hi All,
> > >>
> > >> Thanks so much for all the responses. I will take Izzy's idea to try. Do
> > >> it
> > >> in test system to see. Also the data which has been in the column is like
> > >> "mmm dd yyyy hh:mm:ss am or pm". How would I convert old data into
> > >> datatime
> > >> type in the new column? I like to keep old and new data in the same
> > >> format
> > >> like "mmm dd yyyy" or "mm/dd/yy". Is it possible? If you can show me the
> > >> function that would be great.
> > >>
> > >> Thanks again,
> > >> Sarah
> > >> "Izzy" <israel.richner@.gmail.com> wrote in message
> > >> news:1153241956.118537.211970@.h48g2000cwc.googlegroups.com...
> > >> > Another suggestion would be to create a new column and give it a data
> > >> > type of DateTime. Then run an update statement to copy the data from
> > >> > var column to the new column.
> > >> >
> > >> > IF the copy process finishes without error...delete old column.
> > >> >
> > >> > Whatever you do, make sure you backup your system to a test environment
> > >> > and convert it there before converting your production machine. Also be
> > >> > sure to verify the dates for accuracy.
> > >> >
> > >> > That's why I like my 2 column method, you'll be able to verify the data
> > >> > before dropping the original column.
> > >> >
> > >> > In the future it would be helpful if you posted sample data and gave
> > >> > some specifics like how many rows this table contains.
> > >> >
> > >> >
> > >> > Sarah wrote:
> > >> >> Hi,
> > >> >>
> > >> >> I'm using SQL 2000. I have table called invoice which has 2 cols
> > >> >> "invoice"
> > >> >> (var 10) / "invoicedate" (var 25).
> > >> >> The question will rise up that why I defined col "invoicedate" as var.
> > >> >> That's my mistake when I was new to sql table design. At the front end
> > >> >> of
> > >> >> the application, user scans the invoices into this table without
> > >> >> seeing
> > >> >> 'invoicedate' field. However I like to keep date info in this column,
> > >> >> so
> > >> >> I
> > >> >> set default value in the table getdate(). By mistake, I defined the
> > >> >> type
> > >> >> as
> > >> >> var instead of date. Now I'm getting into problem. There are tons of
> > >> >> records
> > >> >> in this table. I like to query the table by date which it's so hard.
> > >> >> I like to know at this point, can I change the type to date? Would it
> > >> >> cause
> > >> >> to lose the data? Or if you have better idea that would be great help.
> > >> >> I hope I explained the problem properly, if you have any questions,
> > >> >> please
> > >> >> feel free let me know.
> > >> >>
> > >> >> Thanks,
> > >> >> Sarah
> > >> >
> > >