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 2012 Forums
 Transact-SQL (2012)
 Help compairing the time in DateTime to VARCHAR.

Author  Topic 

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-07-26 : 10:14:01
I am trying to update a proc and need to Compare a DATETIME variable named "StartDate" which contains a full Date and Time in 24 hour format. This variable needs to have it's time component compared to a table column (named "StrTm") that is VARCHAR(10) and holds only a 4 digit number 0000 - 2359 to represent a 24 hour time format. I know I can use CONVERT(VARCHAR(10), @StartDate, 114) to get the Hour and minutes in a 24 hour format but that also gives me seconds, milliseconds, and the : separator. All of which make the comparison to the StrTm column not work. What is the easiest way to extract just the hour and minutes in a 24h format with no separators so I can properly compare @StartDate to StrTm?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-26 : 10:29:14
Take only the parts you want from what you converted, for example using LEFT and REPLACE like this:
REPLACE(LEFT(CONVERT (VARCHAR(10), @StartDate, 114),5),':','')
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-07-26 : 10:30:14
Thanks, that looks to be working quite well.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-26 : 10:36:18
You are very welcome.

Before someone else does it let me take this opportunity to be harsh on you for storing time in a string column. I don't want to describe the reasons, I want to let someone else do that ;)
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-07-28 : 10:02:19
From 2008 onwards you can make use of TIME datatype to store time values

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -