Changing datatype from varchar to datetime

Results 1 to 2 of 2

Thread: Changing datatype from varchar to datetime

  1. #1
    Join Date
    Dec 1969

    Default Changing datatype from varchar to datetime

    I need to change a varchar column to datetime. The column has dates in it gathered by using GetDate() in the SQL statement. Looks like... Oct 4 2004 3:52PM<BR><BR>But I need it to look like this instead--&#062; 10/4/2003 15:52:28 PM so I can compare dates in a SQL statement...and because I like it better that way...and datetime is the datatype is *should* be anyway.<BR><BR>TWO parts to this question:<BR><BR>1) When I attempt to chage the datatype from varchar(50) to the default datetime(8), I get the error that says that some data may be lost because the new length is shorter than the original length. Will doing this actually delete/truncate any data?<BR><BR>2) The dates that are already in the database are like &#039;Oct 4 2004 3:52PM&#039; ...Will they automatically change to the format I want when I make the datatype change? If no, how can I convert these dates to how I want?

  2. #2
    Join Date
    Dec 1969

    Default Why didn't you just try it???

    Back up your DB and try it.<BR><BR>Or copy the table into a temp table using something like<BR> SELECT * INTO temptable FROM oldtable<BR>and then try it in the temptable.<BR><BR><BR>I do have to ask how you got the dates into the varchar column in the first place. Did you really just use GetDate() without using any CONVERT to drive the format of the string??<BR><BR>ALSO...<BR>I need it to look like this instead--&#062; 10/4/2003 15:52:28 PM <BR><BR>It won&#039;t. It will be stored as two 32-bit integers. Which is how it *should* be. The &#039;10/4/2003 2:38:17 AM&#039; format is *strictly* for human consumption. The database never actually uses those strings when comparing dates and times. It converts them to internal format first and *then* compares/stores/etc. with them.<BR><BR>See the ASPFAQs (link at top center, above) and the category "Dates and Times" for a discussion of how these things *really* work.<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts