Solved

SQL Convert to DateTime

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

777 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