Need to "build" a date-time field from separate HHMM-field

I'm working with a database structure that I cannot change - in SQL Server 2005 environment. There are two fields -- a complete date-time field, and an HHMM field.

The software application doesn't always populate both fields, so I need to create a complet开发者_如何转开发e date-time field. If the hour-minute-second portions of the date-time field is zero, then I need to create a complete date-time from the date portion of the date-time field, the hour of the HHMM field, the minute of the HHMM field and zero seconds.

How can I do this?

This updates all the date-time values that have no time parts, with the time portions from HHMM:

UPDATE atable
SET DateTime = DateTime + STUFF(HHMM, 3, 0, ':')
WHERE DateTime = DATEADD(day, DATEDIFF(day, 0, DateTime), 0)

It is assumed that the HHMM column is a string of a fixed format, i.e. you always have 2-digit hours and 2-digit minutes in HHMM, like '1400' or '0825'. The query inserts : between the time parts and adds the resulting time (represented as a string) to the date-time column value.

If HHMM is an integer, it can lack the leading zero or zeros for times like 08:00 or 00:05 (which would be stored, respectively, as 800 and 5). So, before inserting the colon, we'll need to bring the value to a string formatted as HHMM, e.g. like this:

RIGHT(10000 + HHMM, 4)

Now we can pass this expression to the above query:

UPDATE atable
SET DateTime = DateTime + STUFF(RIGHT(10000 + HHMM, 4), 3, 0, ':')
WHERE DateTime = DATEADD(day, DATEDIFF(day, 0, DateTime), 0)

Cythia:

What programming language are you using? You probably want to look for a function like createDate() or createDateTime(), which typically accepts string inputs and a format mask. You probably also will have functions like Now() and Today() which will generate a time variable based on the current time. If the language you're using is strictly typed, you may have to use a function like ConvertToString() or AsString(). Of course all these function names will vary widely from language to language.

So here is a bit of step-by-step 1. Get your initial dateTime variable 2. Create a new date variable (i.e. MYDATE), and set it using a DateFormatFunction() that returns only the date in the format you want specified by a mask (i.e. MM/DD/YYYY) 3. Create a new time variable (i.e. MYTIME), and it using a TimeFormatFunction() that returns only the date in the format you want specified by a mask (i.e. HH:MM:SS) 4. Check to see if MYTIME is 00:00:00. 5. If it is, create a new time variable (i.e. MYTIMENOW), and set it to the current time using something like Now("HH:MM:SS"), using the appropriate mask. 6. Now, depending on what format the date is in MS SQL, you need to build a properly formated date by creating a new DateTime variable (i.e. MYDATETIME) and create it using a CreateDateTime("MYDATE MYTIME") function that allows your to mask it accordingly. 7. Insert the resulting MYDATETIME variable into your database.

If you let me know your language, I might be able to tell you some of the caveats, as there always are "gotchas" when working with date/times across platforms.