Solved

SQL Convert to DateTime

Posted on 2016-08-22
4
55 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
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

856 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