473,320 Members | 1,965 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

convert int date to datetime

Hello,
I have a table that unfortunatley has a field 'DateSold' with datatype
of int that SHOULD be datetime.

So, I am trying to do a cast/convert. The int is stored as 20040520
(which means 5/20/2004)

I tried saying Select col1 from table where cast(DateSold as DATETIME)
'1/1/2004'


but of course it bombs out.

What is the most efficient way to do this? Is there a way to cast it
without doing some crazy string manipulation?

Jul 23 '05 #1
3 19727
th*****************@yahoo.com wrote:
So, I am trying to do a cast/convert. The int is stored as 20040520
(which means 5/20/2004)
What is the most efficient way to do this? Is there a way to cast it
without doing some crazy string manipulation?


Maybe there's a clever, efficient way of doing it, but you don't need
the absolute best way, you just need to get it done, so you can
subsequently use a real DATE field.

For instance, I'd probably do something like this:

ALTER TABLE `table` CHANGE COLUMN DateSold OldDateSold INT;
ALTER TABLE `table` ADD COLUMN DateSold DATE;
UPDATE `table` SET DateSold =
DATE(CONCAT(
OldDateSold DIV 10000, '-',
(OldDateSold DIV 100) % 100, '-',
OldDateSold % 100)
);
....verify everything happened as you intended it to...
ALTER TABLE `table` DROP COLUMN OldDateSold;

Try this on a copy of your database first to make sure it works! :)

Regards,
Bill K.
Jul 23 '05 #2
Unfortunatley I am not the DBA, or I would have done this first thing.
I already suggested changing it from int to datetime and got shot down
quickly.

So, is there a way to do it? thx for the quick reply.

Jul 23 '05 #3
th*****************@yahoo.com wrote:
Unfortunatley I am not the DBA, or I would have done this first thing.
I already suggested changing it from int to datetime and got shot down
quickly.
Sorry about that. I did not understand that was the situation. How
unfortunate!
So, is there a way to do it? thx for the quick reply.


How about the STR_TO_DATE() function, and use the integer value in place
of the string, so that it implicitly casts it to a string?

SELECT STR_TO_DATE(DateSold, '%Y%m%d')
FROM `table`;

Regards,
Bill K.
Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Sorisio, Chris | last post by:
Ladies and gentlemen, I've imported some data from a MySQL database into a Python dictionary. I'm attempting to tidy up the date fields, but I'm receiving a 'mx.DateTime.Error: cannot convert...
1
by: Loi Tan Vo | last post by:
Hi, I have a text file that contains a date column. The text file will be imported to database in SQL 2000 server. After to be imported, I want to convert the date column to date type. For...
5
by: goochey | last post by:
I'm trying to convert a Julian Date (Format "4365") into an actual calendar date in Visual Basic, can anyone help me out with this.
1
by: Islam Elkhayat | last post by:
In my C# Webapplication.. I enter the date in a textbox using popup Calender in the format MM/dd/yyyy.. when i retrieve date from Sql server for updating using datarow to fill the textbox the...
5
by: simon | last post by:
I have datetime variable: Datetime tsEndTime; Should I use (DateTime): tsEndTime=(DateTime)rdr.GetValue(15) or is better to use: tsEndTime=Convert.ToDateTime(rdr.GetValue(15))
6
by: Richard | last post by:
Hi all, Hope everybody is having an OK monday?!!? Quick question, I have a database in which the dates are stored as longs. I think the original program was VB and the dates were just cast...
2
by: ziggislaw | last post by:
hello how can I convert DateTime from "25.12.2005" to "2005-12-25 00:00:00.000" ? Now I have DateTime as string (I get it from <asp:label>). Thanks
17
by: Terry Jolly | last post by:
New to C# ---- How do I convert a Date to int? In VB6: Dim lDate as long lDate = CLng(Date) In C#
1
by: Ryan Ramsey | last post by:
I am trying to convert a value returned from the date() function in php 5.0 to a format .NET can use. DateTime dt_now = DateTime.Now; DateTime dt_last = new DateTime(Convert.ToInt32(dkpLast));...
2
by: kirke | last post by:
Hi, I have a datetime column named dtDateTime. its format is "Oct 27 2006 12:00:00 " I want to group by only date part of it and count my code is $sql1="SELECT ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.