Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

can we compare time AM/PM time format? Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 8:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:10 AM
Points: 181, Visits: 467
Hi,

Can we compare AM PM date format?

Example datecolumn > 12:00PM and datecolumn < 1:00PM.

Here datecolumn is varchar. Pleae suggest any idea?

Thanks
Abhas.
Post #1443303
Posted Wednesday, April 17, 2013 8:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 809, Visits: 1,158
Covert them into datetime & compare.
Post #1443320
Posted Wednesday, April 17, 2013 9:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:10 AM
Points: 181, Visits: 467
can u pls send sample code?
Post #1443327
Posted Wednesday, April 17, 2013 9:28 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
abhas (4/17/2013)
can u pls send sample code?


Sure, once you post your code. Of course once you do that we may ask for more, but it is a start.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1443338
Posted Wednesday, April 17, 2013 10:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:10 AM
Points: 181, Visits: 467
Yes Lynn,

I am able to select lunch time for students using CTE as below. But not able to set flag 1 in the main student table.

DECLARE @t TABLE
(StudId INT, StartDate datetime, EndDate datetime,StartTime datetime, EndTime datetime, LunchStart datetime,LunchEnd datetime,flag bit)

INSERT INTO @t
SELECT 555,'2012-01-01 00:00:00', '2012-01-02 00:00:00','2012-01-03 07:00:00', '2012-01-03 16:00:00','2012-01-03 13:00:00','2012-01-03 14:00:00',0
;WITH Tally1 (n) AS (
SELECT TOP 100 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)
FROM sys.all_columns)

SELECT StudId,
dateadd(day, b.n-1, startdate) as effectivedate
,Tstart = RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, LunchStart)), 0),7)
, TsEnd = RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, a.N*15, LunchStart)), 0),7)


FROM @t
cross join Tally A
cross join Tally B
WHERE
a.N >= 1 and a.N <= datediff(mi, LunchStart, LunchEnd)/15 and
b.N >= 1 and b.N <= DATEDIFF(day, startdate,enddate) + 1

ORDER BY effectivedate
Post #1443361
Posted Wednesday, April 17, 2013 11:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
Not sure what you are expecting but running the following all I see in the output is 1:00 PM to 2:00 PM which corresponds to the LunchStart and LunchEnd for the sample student:


DECLARE @t TABLE
(StudId INT, StartDate datetime, EndDate datetime,StartTime datetime, EndTime datetime, LunchStart datetime,LunchEnd datetime,flag bit);

INSERT INTO @t
SELECT 555,'2012-01-01 00:00:00', '2012-01-02 00:00:00','2012-01-03 07:00:00', '2012-01-03 16:00:00','2012-01-03 13:00:00','2012-01-03 14:00:00',0;

WITH Tally1 (n) AS (
SELECT TOP 100
15 * (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1)
FROM
sys.all_columns
)
SELECT
StudId, LunchStart, LunchEnd,
dateadd(day, b.n-1, startdate) as effectivedate,
a.n,
b.n,
datediff(mi, LunchStart, LunchEnd)/15,
DATEDIFF(day, startdate,enddate) + 1,
Tstart = RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, LunchStart)), 0),7),
TsEnd = RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, a.N*15, LunchStart)), 0),7)
FROM @t
cross join Tally A
cross join Tally B
WHERE
a.N >= 1 and a.N <= datediff(mi, LunchStart, LunchEnd)/15 and
b.N >= 1 and b.N <= DATEDIFF(day, startdate,enddate) + 1

ORDER BY effectivedate ;



By the way, CTE's don't start with a semicolon (;). The semicolon is a statement TERMINATOR not a statement BEGININATOR and belongs on the statement immediately preceding the WITH of a CTE declaration.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1443392
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse