Link to home
Start Free TrialLog in
Avatar of CipherIS
CipherISFlag for United States of America

asked on

SQL - Convert VARCHAR to DateTime

I am trying to print out a date with today's date.  The result i'm looking for is 9/18/2017 23:59:59.  Or it can be 2017-09-20 23:59:59.
DECLARE @sDate AS VARCHAR(25)
SET @sDate = CONVERT(VARCHAR, GETDATE(), 101) + ' 23:59:59.999'
SELECT @sDate

DECLARE @Date AS DATETIME
SET @Date = CONVERT(DATETIME, @sDate, 102)
--SET @Date = @sDate
SELECT @Date

Open in new window

Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>The result i'm looking for is 9/18/2017 23:59:59.
Expand on why you're trying to do this, as it would be better to do it this way to avoid any 23:59:59994 issues..

Declare @sDate date, @eDate date
SELECT @sDate = CAST(GETDATE() as date) 
SELECT @eDate = DATEADD(day, 1, @sDate) 

--- then in queries
SELECT blah, blah, blah
FROM your_table
WHERE some_date_column >= @sDate and some_date_column < @eDate

Open in new window


Also tell us why you're putting a perfectly good date value into a varchar column.
Avatar of CipherIS

ASKER

@Jim.  Your solution doesn't work.
>Your solution doesn't work.
Some details would help.  Mind readers we ain't.
Pls try this - do not case to datetime...use this format only.

SELECT  CONVERT(VARCHAR, GETDATE(), 101)  + ' ' + CAST('23:59:59.999' AS VARCHAR(15))  
SELECT  CONVERT(VARCHAR, GETDATE()+1, 101)  + ' ' + CAST('23:59:59.999' AS VARCHAR(15))  

Open in new window

try this
dateadd(second, -1, cast(cast(getdate()+1 as date) as datetime))

Open in new window


demo:

select getdate(), dateadd(second, -1, cast(cast(getdate()+1 as date) as datetime))

2017-09-19 15:58:36.763	2017-09-19 23:59:59.000

Open in new window

CAST to DATE and it will not work. VARCHAR one i have already given the solution. :)

select getdate(), dateadd(second, -1, cast(cast(getdate()+1 as date) as datetime))

DECLARE @sDate AS VARCHAR(25)
SET @sDate = CONVERT(VARCHAR, GETDATE(), 101) + ' 23:59:59.999'
SELECT CAST(@sDate AS DATETIME)

-----------------------
2017-09-21 00:00:00.000
then you can use some formatting

How to convert from string to datetime?
http://www.sqlusa.com/bestpractices/datetimeconversion/

like

convert(varchar, someDateTime, 120) -- yyyy-mm-dd hh:mm:ss(24h)

Open in new window


demo
select getdate(),
       dateadd(second, -1, cast(cast(getdate()+1 as date) as datetime)),
       convert(varchar, dateadd(second, -1, cast(cast(getdate()+1 as date) as datetime))),
       convert(varchar, dateadd(second, -1, cast(cast(getdate()+1 as date) as datetime)), 120)

2017-09-19 16:05:13.363	2017-09-19 23:59:59.000	Sep 19 2017 11:59PM	2017-09-19 23:59:59

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Russell Fox
Russell Fox
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Jim. If you would have run your code you would have seen it didn't meet the requirements that I was looking for.
To put it plainly your requirements are flawed, as adding a 23:59:59 to the end of a date value, thinking that the entire date is covered, will not capture any values between 23:59:59 and the next day, such as 323:59:599954.
@Jim.  I have no problems with your suggestion regarding adding the :599954 but your code returned 00:00:00 which is NOT what I was looking for.  Thanks.
what happened to the solutions provided above (ID: 42300504 and ID: 42300512):

ie:

DECLARE @sDate AS VARCHAR(25)
SET @sDate = convert(varchar, dateadd(second, -1, cast(cast(getdate()+1 as date) as datetime)), 120)
SELECT @sDate

2017-09-20 23:59:59

Open in new window


it is working fine and giving what you ask for!
@HainKurt's solution does also work so feel free to split the points with him. Note also that starting in SQL 2012 they added the FORMAT function that gives you fine-grained control of displaying date/times however you want. It might be time to upgrade!