CipherIS
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
ASKER
@Jim. Your solution doesn't work.
>Your solution doesn't work.
Some details would help. Mind readers we ain't.
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))
try this
demo:
dateadd(second, -1, cast(cast(getdate()+1 as date) as datetime))
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
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
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
demo
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)
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.
ASKER
@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:
it is working fine and giving what you ask for!
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
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!
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..
Open in new window
Also tell us why you're putting a perfectly good date value into a varchar column.