July 4, 2005 at 2:08 am
I have a field called startDateTime and a field called endDateTime (as varchars) both in the format "dd/mm/yyyy hh:mm:ss"
I also have a field called dateOnlyStart and dateOnlyEnd both in the format dd/mm/yyyy. However, only some of these fields are populated and the rest are all <NULL>.
Is it possible to take the first 10 characters of all of the startDateTime (dd/mm/yyyy) entries and place them all in the dateOnlyStart (that applies to that entry), and take the first 10 characters of the endDateTime entries and place them in the dateOnlyEnd column (that applies to that entry). (So if startDateTime was 04/07/2005 09:00:17, then only 04/07/2005 would be copied into dateOnlyStart)
Thanks Everyone
Dave
July 4, 2005 at 2:45 am
Well, yes, it would be possible. But there are some questions here, regarding how the data is stored. First of all, you should not be storing redundant data. Storing date and time in one column and date only in another is storing the same data (the date part) twice. This is bound to cause problems, and is of course a waste of space. Separating the date from the time part if that is needed should be done while retrieving the data, and it should probably be done on the client.
Next, are you actually storing your datetimes in character type columns, or are they stored in columns of type datetime? They should definitely be stored in datetime columns, so you can use the available datetime manipulation functions for handling them. SQL Server does not store datetime data in a format such as "dd/mm/yyyy hh:mm:ss". That is only a representation of the data, and just one way to represent it. By converting the data to character data you can specify which way to display it. Take a look at these excellent articles regarding datetime data and how to handle it:
Demystifying the SQL Server DATETIME Datatype by SQL Server MVP Frank Kalis
The ultimate guide to the datetime datatypes by SQL Server MVP Tibor Karaszi
To answer your question simply though: If your startDateTime column actually stores data in datetime type, and the dateOnlyStart column is a char(10), then you could do something like this:
UPDATE tablename SET dateOnlyStart = CONVERT(CHAR(10), startDateTime, 103)
Of course if startDateTime is stored in a character type column as well you would just use LEFT(startDateTime, 10) to get the first 10 characters.
But, like I stated above, this design does not look like the best possible, and you should definitely study the links I supplied above to correct it.
July 4, 2005 at 3:17 am
Hey Chris
Thanks for replying.
I think I may have to do it using textfields thought. The data is sent from a system (from a textfield to a varchar) to the SQL Server database. The system sends the data in the format dd/mm/yyyy-hh:mm:ss. An ASP page takes this data and splits up the date e.g. left(date,10) and passes this information to a new field in the database. I'm doing this as I've connected this to a spreadsheet (Tools, Get External Data) which has a macro. When the spreadsheet is opened the user types in two dates (in the fomat dd/mm/yyyy) and all data between those dates is returned to the spreadsheet. So I though I would need to keep them as textfields, this is also why I have duplicate data/fields as it means the spreadsheet user doesn't have to type in time values when carrying out searches.
I'll have a look at the stuff you said to and see how I get on though. I'll post back to say how I got on.
Cheers
Dave
July 4, 2005 at 3:43 am
The following code worked fine Chris
UPDATE CallLogRevenuesTEST
SET dateOnlyEnd = LEFT(endDateTime, 10)
Thanks a lot
Dave
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply