Solved

SQL Convert to DateTime

Posted on 2016-08-22
4
60 Views
Last Modified: 2016-08-22
How do I convert the STDStartDate & STDEndDate to a datetime value to pass into my query  versus text?

SELECT  DATEADD(wk, 0,
                DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()),
                        DATEDIFF(dd, 0, GETDATE()))) AS WkStartDate ,
        DATEADD(wk, 1,
                DATEADD(DAY, 0 - DATEPART(WEEKDAY, GETDATE()),
                        DATEDIFF(dd, 0, GETDATE()))) AS WkEndDate ,
        DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AS MthStartDate ,
        DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)) AS MthEndDate ,
        DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS YearStartDate ,
        DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS YearEndDate,
        [b]CASE WHEN GETDATE() <= '2016-06-30' THEN '2016-01-01' ELSE '2016-07-01' END AS STDStartDate,
        CASE WHEN GETDATE() <= '2016-06-30' THEN '2016-06-30' ELSE '2016-12-31' END AS STDEndDate;
[/b]

Open in new window

0
Comment
Question by:mburk1968
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 125 total points
ID: 41765826
CAST('2016-06-30' as datetime)

This assumes that the value is convertable to date time, and you can test that like this..

CASE
   WHEN ISDATE('2016-06-30') = 1 THEN -- good
   ELSE -- not a date
END
1
 
LVL 25

Assisted Solution

by:jrb1
jrb1 earned 125 total points
ID: 41765866
And you can apply the CAST one time for each field:

SELECT  DATEADD(wk, 0,
                DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()),
                        DATEDIFF(dd, 0, GETDATE()))) AS WkStartDate ,
        DATEADD(wk, 1,
                DATEADD(DAY, 0 - DATEPART(WEEKDAY, GETDATE()),
                        DATEDIFF(dd, 0, GETDATE()))) AS WkEndDate ,
        DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AS MthStartDate ,
        DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)) AS MthEndDate ,
        DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS YearStartDate ,
        DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS YearEndDate,
        CAST(CASE WHEN GETDATE() <= '2016-06-30' THEN '2016-01-01' ELSE '2016-07-01' END as datetime) AS STDStartDate,
        CAST(CASE WHEN GETDATE() <= '2016-06-30' THEN '2016-06-30' ELSE '2016-12-31' END as datetime) AS STDEndDate;
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 250 total points
ID: 41765877
Your logic in natural language would be

Startdate is 1st of January of the year for current date in the first half of the year.
Startdate is 1st of July of the year for current date in second half of the year.

Enddate is last day of June vs last day of December in first/last half.

Correct?

OK, so lets simple tackle what expressions to use for the dates and the first/second half year periods.

Let's see at some expressions for the dates in question:

Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AS January1,
DATEADD(qq, DATEDIFF(yy,0,getdate())*4+2, -1) AS June30,
DATEADD(qq, DATEDIFF(yy,0,getdate())*4+2, 0) AS July1,
DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1) AS December31

Open in new window


We don't need the 30th of June, though, the exact half of the year (well, in month exactness) is 1st July midnight, just the moment before June ends. In the same way you rather need the 1st of January next year as endpoint.

And no matter how exactly you judge that, <= '2016-06-30' is wrong, it leaves the full day of 30th June out, you would test for <'2016-07-01' to take everything up to the moment before midnight July, 1st to the first half.

You can put GetDate() into the first or second half of a year much simpler though, by testing quarter being1,2 vs 3,4.

 So let's see this:

Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AS January1,
DATEADD(qq, DATEDIFF(yy,0,getdate())*4+2, 0) AS July1,
DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, 0) AS January1NextYear,
DATEPART(qq,GETDATE()) as CurrentQuarter

Open in new window


So finally, the start and enddate as you need them:
CASE WHEN DATEPART(qq,GETDATE()) <3 THEN DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) ELSE DATEADD(qq, DATEDIFF(yy,0,getdate())*4+2, 0) END AS STDStartDate

Open in new window


and
CASE WHEN DATEPART(qq,GETDATE()) <3 THEN DATEADD(qq, DATEDIFF(yy,0,getdate())*4+2, 0) ELSE DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, 0) END AS STDEndDate

Open in new window


Bye, Olaf.
1
 

Author Comment

by:mburk1968
ID: 41765901
Awesome! Thank You
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question