Getting time from datetime field

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Getting time from datetime field

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2013-10-18 : 05:54:44

Hi There,

I need to do a query on the time element in a datetime field.

I have tried this:
SELECT My_Tbl.AutoId, My_Tbl.EntryDate, CONVERT(VARCHAR(8),My_Tbl.EntryDate,108)As Test
FROM My_Tbl
Where Test = '07:55:47'

But I get this error:
Invalid column name 'Test'.

How do I reference the converted column name.

Thanks for your help.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 08:46:45

[code]
SELECT *
FROM
(
SELECT My_Tbl.AutoId, My_Tbl.EntryDate, CONVERT(VARCHAR(8),My_Tbl.EntryDate,108)As Test
FROM My_Tbl
)t
Where Test = '07:55:47'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Steve2106
Posting Yak Master

183 Posts

Posted - 2013-10-18 : 10:00:03

Thanks visakh16.

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-10-19 : 06:25:13

Or....

SELECT
My_Tbl.AutoId, My_Tbl.EntryDate,
CONVERT(VARCHAR(8),My_Tbl.EntryDate,108)As Test
FROM My_Tbl

Where Cast(My_Tbl.EntryDate as time)= '07:55:47'

Marcus Night

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-20 : 03:57:36

quote:
Originally posted by marcusn25

Or....

SELECT
My_Tbl.AutoId, My_Tbl.EntryDate,
CONVERT(VARCHAR(8),My_Tbl.EntryDate,108)As Test
FROM My_Tbl

Where Cast(My_Tbl.EntryDate as time)= '07:55:47'

Marcus Night

Will work only from SQL 2008 onwards as date,time etc available only from SQL 2008

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources