Convert date and time

[timscronin]

I have 2 fields one with date like below, other is time like below but no milliseconds. Other than manully adding in all / and : is there a faster convert to combine the 2 for date/time comparison

date time
20060303 150000
20070601 120000

 

[onpnt]

You should have no problem using the CONVERT function with those two fields to get a valid datetime result

try faq183-6252 for assistance

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[gmmastros]

Well... It depends on the type of comparison, but since you have this data as integers already, you could....


Select Convert(BigInt, DateColumn) * 1000000 + TimeColumn

Eventhough the result will be a big int, you can still use it for comparison purposes. You won't be able to do this for date arithmetic like datediff, dateadd, etc..., but comparing it to other data that is in the same format should work just fine.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

Put them in datetime field and use it and don't split datetime again
Other way:

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]CAST[/color](MyDateField+[COLOR=red]' '[/color]+[COLOR=#FF00FF]SUBSTRING[/color](TimeField,1,2)+[COLOR=red]':'[/color]+
                            [COLOR=#FF00FF]SUBSTRING[/color](TimeField,3,2)+[COLOR=red]':'[/color]+
                            [COLOR=#FF00FF]SUBSTRING[/color](TimeField,5,2)
            [COLOR=blue]as[/color] [COLOR=#FF00FF]datetime[/color])

BUT if you have indexes on these fields it is better to convert the datetime variable you passed to query to string just to match these indexes.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[monksnake]

What about comparing the values as decimal after a string concatenation?

select convert(decimal, 
         (convert(char(8), [date]) 
      + 
          convert(char(6), [time]))
       ) 
FROM table1

<.