Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Set Hours in DateTime variable

Status
Not open for further replies.

pdbowling

Programmer
Mar 28, 2003
267
US
Hello, everyone.

I have a parameter coming in to a stored procedure.

ALTER PROCEDURE [dbo].[udp_GetPriority]
@CurrentDateTime DATETIME

It's defaulted to 12:00AM as the hour, and I'd like to set the time in the T-SQL statement before running my query.

I didn't see an obvious method jump out as I was digging through the help files on DateTime types.
I saw where you can pull things out with DatePart() but not the syntax or methods to write back.

Any suggestions would be great.

(PS: I don't have the source code of the calling program or I'd just have it send in the time with the hour set as needed.)
Thanks, All.
Patrick
 
if you're going to override the value you're given, just do:

set @currentdatetime = getdate()

If you trust that the variable will always have today's date in it, override it. If you don't trust it will always have today's date in it, ever adding today's time to it is problematic.
 
Code:
DECLARE @Test DATETIME
SET @Test = '20071022'
SELECT @Test
 -- You should get 22 Oct 2007 12:00:00 AM as a result

SELECT DATEADD(ss, 18*3600+27*60+31, @Test)
 -- You should get 22 Oct 2007 06:27:31 PM as a result

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP
 
Boris. You missed one. [smile]

Code:
DECLARE @Test DATETIME
SET @Test = '20071022'

SELECT @Test
 -- You should get 22 Oct 2007 12:00:00 AM as a result

[!]SELECT @Test + '6:27:31 PM'[/!]
[green] -- You should get 22 Oct 2007 06:27:31 PM as a result[/green]

SELECT DATEADD(ss, 18*3600+27*60+31, @Test)
 -- You should get 22 Oct 2007 06:27:31 PM as a result
[code]

This will ONLY work if the time portion of the datetime is midnight.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
George,
No, I didn't miss that, but I don't know why I prefer to add seconds to date to get the datetime. Maybe because when I started with SQL Server I always get error or wrong result when I tried to convert a string to datetime :)
But you are right, that possibility should be mentioned.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP
 
Many thanks to all of you.
bborissov,
gmmastros,
ESquared

This will resolve my difficulties.
Patrick
 
I have a date/time field with the following data as an example: '4/8/2008 12:03:14 AM'. Need the correct syntax in SQL to pull out just the date. Can anybody help? Thanks...
 
Hi,

You can get only date part by this SQL.

Code:
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CAST('4/8/2008 12:03:14 AM' AS DATETIME))))



Koichi
(SQLServer & Cognos Tips)
 
dateadd(day, 0, datediff(day, 0, YourDateColumnOrVar))
 
noodles1, I think there's an error in your expression. Reverse the 2nd and 3rd operands in your dateadd:

dateadd(day, datediff(day, 0, YourDateColumnOrVar), 0)
 
<thinking...> yes you're right. Silly me.

But wait, that exposes that all we're doing with the dateadd is converting the date serial number back to a datetime data type, because with your syntax you're logically adding 0 days to a date instead of adding a number of days to date 0. In fact, the following all work with your syntax:

dateadd(mm, 0, datediff(day, 0, YourDateColumnOrVar))
dateadd(mi, 0, datediff(day, 0, YourDateColumnOrVar))
dateadd(yy, 0, datediff(day, 0, YourDateColumnOrVar))
dateadd(hh, 0, datediff(day, 0, YourDateColumnOrVar))
dateadd(ss, 0, datediff(day, 0, YourDateColumnOrVar))
dateadd(ms, 0, datediff(day, 0, YourDateColumnOrVar))
dateadd(ww, 0, datediff(day, 0, YourDateColumnOrVar))

(assuming I'm remembering the codes for each time unit right)

To me this is now a final and compelling argument why this construct is better avoided in favor of either the operand order I suggested or an explicit conversion:

convert(datetime, datediff(day, 0, YourDateColumnOrVar))

I can even see an argument for:

convert(datetime, datediff(day, '1900-01-01', YourDateColumnOrVar))
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top