Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gstg007
Starting Member
2 Posts |
 Posted - 2005-07-08 : 12:23:44
|
| Hey everyone,I've been experiencing a very strange problem since yesterday. While importing using a DTS from a .csv file, found out a strange problem. In the file which our client is sending us, we have a date field and a time field. When we import it to put it in our SQL tables, we want to put it in a single field which is in DATETIME. Tried the simple concatenation [DATE_FIELD] + [TIME_FIELD] in the query, but when I do this, I get the date two days before. For example, if I have in the file DATE : 2005-07-08 and TIME : 12:40PM, the concatenation results in DATE_TIME_FIELD : 2005-07-06 12:40:00.000. If I use the following, DATEADD(day, 2, [DATE_FIELD]) + [TIME_FIELD]), I get the good result, but I'm definitely not supposed to modify the field. What if someday, the import goes well and that instead of getting "2005-07-08" for an answer, I get "2005-07-10", I have to start over the file import, and we receive a file once a week... So much fun, eh? I'd really need some help on this!!!Thanks a lot,Guillaume |
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-07-08 : 13:38:30
|
| Hi,I think you may want to make sure that the concatenation [DATE_FIELD] + [TIME_FIELD] is a character concatenation, not a numeric addition.You want to end up with a string value that SQL converts to a datetime. I think that numeric addition is occuring, which is causing incorrect dates.Cheers. |
 |
|
|
gstg007
Starting Member
2 Posts |
Posted - 2005-07-08 : 15:34:35
|
| Hey,Finally, I was able to do it doing this way : dateadd(ss, datepart(ss, [TIME_FIELD]), dateadd(mi, datepart(mi, [TIME_FIELD]), dateadd(hh, datepart(hh, [TIME_FIELD]), [DATE_FIELD])))... it worked perfectly actually!The problem was caused by the fact that the time field had the value 1899-12-30 12:40:00.000. So when I added the date, i had this 2 days difference (1900-01-01)... Always good to know, right?Later,Guillaume |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-07-08 : 15:59:17
|
If [DATE_FIELD] and [TIME_FIELD] are both datetime values, then this should work also; just add two days to [TIME_FIELD].[DATE_FIELD] + dateadd(dd,2,[TIME_FIELD]) Or this should work also:dateadd(dd,2,[DATE_FIELD]+[TIME_FIELD]) CODO ERGO SUM |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-07-08 : 23:30:48
|
| If your columns are both datetime, you can also do this:[DATE_FIELD] + CONVERT(char(8), [TIME_FIELD], 108)If, for example, DATE_FIELD is character datatype, then just do a conversion to datetime on it. This way, you don't have to add 2 days. |
|
|
|
|
|
|
|
|
Date-2 problem