Help understanding a convert statment.

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 understanding a convert statment.

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-07-01 : 15:18:16

I am trying to add some new logic to an existing T-SQL script at work and understand most of the script, the only part giving me real trouble is:

Convert(Varchar(10),dtwrkd,101) + Case When strttm is not null and strttm != '' Then ' ' + substring(strttm,1,2) + ':' + + substring(strttm,3,2) Else '' End,

I know it is converting the variable dtwrkd (which turns out to be a column name in a source table (and StrTtm is also a column name) to a varchar(10) but the rest of it kind of has me lost.

--
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-01 : 15:24:04

See this example below - what it seems like is, dtwrkd is a column that has a date (perhaps with a time component). The first part is taking that date+time and picking up only the date part. Then, the time portion is stored in strttm as a character string in the HHMM format. The rest of the statement is splitting that and inserting a colon. Run this and you will see what I mean.

DECLARE @dtwrkd DATETIME = '2013-07-01 15:21:01.857';
DECLARE @strttm VARCHAR(32) = '1134'

SELECT  CONVERT(VARCHAR(10), @dtwrkd, 101)
        + CASE WHEN @strttm IS NOT NULL
                    AND @strttm != ''
               THEN ' ' + SUBSTRING(@strttm, 1, 2) + ':' + +SUBSTRING(@strttm, 3, 2)
               ELSE ''
          END

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-07-01 : 15:44:40

Thanks, that makes perfect sense and fits right into what I thought the overall processing was doing.

--
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-01 : 17:32:28

You are very welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources