Date formating problem

Question

Date formating problem

ash.rvp1

Old Hand

Points: 320
More actions
July 11, 2012 at 12:47 pm

#264196

Hi,
I am trying to do date conversion but with no luck. I want in the format YYYY:MM:DD:HH:MM:SS
from 04-01-2012:00:00:00 AM to 04-30-2012:23:59:59 PM
SELECT
DR_Premier_PO_O.DischargeStartDate DateTime,
DR_Premier_PO_O.DischargeEndDate DateTime,
DR_Premier_PO_O.AdmitDateTime
DECLARE @vStartDate as varchar(30), @vEndDate as varchar(30)
SET @vStartDate = CONVERT(varchar(30), DR_Premier_PO_O.DischargeStartDate, 101)
SET @vEndDate = CONVERT(varchar(30), DR_Premier_PO_O.DischargeEndDate, 101) + ' 23:59:58'
SELECT BV.*, CASE
WHEN BV.InpatientOrOutpatient = 'I' THEN BV.AdmitDateTime
WHEN BV.InpatientOrOutpatient = 'O' THEN ISNULL(BV.AdmitDateTime,BV.ServiceDateTime)
END AS DateOfService,
CASE
WHEN BV.InpatientOrOutpatient = 'I' THEN BV.DischargeDateTime
WHEN BV.InpatientOrOutpatient = 'O' THEN ISNULL(BV.DischargeDateTime,BV.ServiceDateTime)
END AS DischDateTime
INTO #TMP_BarVisits
FROM BarVisits BV WITH (NOLOCK) INNER JOIN
AbstractData ABD WITH (NOLOCK) ON
BV.SourceID = ABD.SourceID
AND BV.VisitID = ABD.VisitID
WHERE CASE
WHEN BV.InpatientOrOutpatient = 'I' THEN BV.DischargeDateTime
WHEN BV.InpatientOrOutpatient = 'O' THEN ISNULL(BV.DischargeDateTime,BV.ServiceDateTime)
END between @vStartDate and @vEndDate
AND ABD.Status <> 'NONE'
AND BV.InpatientOrOutpatient = 'O'
AND ABD.PatientClass IN ('SDC','ER')
ORDER BY
DR_Premier_PO_O.AdmitDateTime

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

I'm not sure I understand your issue.

If the columns in the table are defined as DateTime/SmallDateTime then your variables should be defined using the same data type, not varchar. Internally SQL Server stores dates as an interger so the format is just for viewing and not for any comparisons or storage.

Can you provide the DDL for the table(s) that you are querying so we can see the data types? Also can you post some sample data and desired output?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Besides understanding the datatypes, particularly for BV.DischargeDateTime and BV.ServiceDateTime, you should look at modifying how you do your range testing for datetime values.

You really should move to a process that uses a closed ended comparision on the low end (MyDatetimeCol >= @StartDate) and an open ended comparision on the upper end (MyDatetimeCol < @EndDate).

For example, using this date range: 04-01-2012:00:00:00 AM to 04-30-2012:23:59:59 PM, your @StartDate would be '20120401 00:00:00' and your @EndDate would be '20120501 00:00:00'.

ash.rvp1 Old Hand Points: 320 More actions · Answer 3

ash.rvp1

Old Hand

Points: 320

July 12, 2012 at 7:45 am

#1512059

Thank you for your feedback.

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

And be wary of NOLOCK. If there is a good reason you can explain then go for it.

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

ash.rvp1 (7/12/2012)
Thank you for your feedback.

How does DR_Premier_PO_O fit into all this? There are various references to it in your script but none of them make much sense. If it's a table, should it be joined to the rest of the SELECT query? I think if you can explain the connection between DR_Premier_PO_O and your query, you're almost there.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden