Posted by Brad Wood
Mar 26, 2009 08:18:00 UTC
Tonight I found myself needing to extract just the time from a datetime column in SQL Server 2000. After a bit of Googling, it became clear that there are about 1 million articles on how to extract just the date from a datetime, but not very many articles taking about just getting time. For reference, this is how I ended up doing it.In MS SQL, the convert function is very handy for dealing with dates. There are a number of different conversion styles it supports:
[code]SELECT CONVERT(VARCHAR,getdate(),108)[/code]This tells SQL Server to convert the current date/time into a varchar using style 108, which is "hh:mm:ss". The most common conversion style I use is probably 101, or "mm/dd/yy". There are a couple of annoying things about convert. One is that is returns a string. This means that if you want to keep doing date manipulations you usually have to cast it back to a datetime one way or another. I guess technically ColdFusion's dateformat function returns a string, but CF (being typeless) is better as auto-casting those strings back into dates when it can. The other main annoyance is that we just have about 19 preset date/time formats to choose from. I guess ColdFusion spoils us with masks that give us full control.