-
Date & time problems
Hi All,
I'm passing a string representing a number of records to a stored proc. Part of the string are 2 datetime columns. In the stored proc I break-up the string and place into temp vars before updating a number of tables with the values from the vars.
What's strange is the time part of the datetime column change when they are converted back to a datetime field. For example the following is the value orignially from the table:
30/01/2006 13:07:38
after being reconctructed backinto a date field it shows as:
30/01/2006 13:10:04
Another example: 28/02/2006 14:15:14 becomes 28/02/2006 14:15:44
Part of the function I created to do the conversion is below:
.....
--create date string
set @dtePart = @yearNo + '/' + @monthNo + '/' + @dayNo
set @timePart = @hour + ':' + @minutes + ':' + @seconds
--return date object
Return (cast(@dtePart +' '+@timePart as datetime))
Any clues to why this happens?
Thanks in advance for any help!
regards
Davej
-
You need to show us how you are getting all the individual parts of the date/time string to give us a better picture of what's going on. However, if I had to guess, I'd say you are probably doing something like this:
SET @hour = DATEPART(hour, getdate())
SET @minutes = DATEPART(minute, getdate())
SET @seconds= DATEPART(second, getdate())
If this is what you are doing, then the problem is that the value of "getdate()" is going to change from one statement to the next. You need to save "getdate()" to a variable first to make it a static value:
DECLARE @getdate datetime
SET @getdate = getdate()
SET @hour = DATEPART(hour, @getdate)
SET @minutes = DATEPART(minute, @getdate)
SET @seconds= DATEPART(second, @getdate)
If this is not what the problem is, please post the rest of your code.
-
Hi Nosepicker,
Thanks for the reply. Here's the full function
FUNCTION dbo.createDateTime
(
@datetimestring varchar(20)
)
RETURNS datetime
AS
BEGIN
--declare vars
declare @dtePart varchar(10)
declare @timePart varchar(8)
declare @yearNo varchar(4)
declare @monthNo varchar(2)
declare @dayNo varchar(2)
declare @hour varchar(2)
declare @minutes varchar(2)
declare @seconds varchar(2)
declare @dateLen int
declare @buffer varchar(20)
declare @pos int
--init vars
set @dateLen = len(@datetimestring)
set @buffer = @datetimestring
set @pos = charindex('/', @buffer, 1)
--split date
set @dayno = left(@buffer, @pos - 1)
set @buffer = right(@buffer, len(@buffer) - @pos)
set @pos = charindex('/', @buffer, 1)
set @monthNo = left(@buffer, @pos - 1)
set @buffer = right(@buffer, len(@buffer) - @pos)
set @pos = charindex(' ', @buffer, 1)
set @yearNo = left(@buffer, @pos - 1)
set @buffer = right(@buffer, len(@buffer) - @pos)
--split time
set @pos = charindex(':', @buffer, 1)
set @hour = left(@buffer, @pos - 1)
set @buffer = right(@buffer, len(@buffer) - @pos)
set @pos = charindex(':', @buffer, 1)
set @minutes = left(@buffer, @pos - 1)
set @buffer = right(@buffer, len(@buffer) - @pos)
set @seconds = @buffer
--create date string
set @dtePart = @yearNo + '/' + @monthNo + '/' + @dayNo
set @timePart = @hour + ':' + @minutes + ':' + @seconds
--return date object
Return (cast(@dtePart +' '+@timePart as datetime))
END
Here's an exaple of the string passed in:
exec createDatetime( '30/01/2006 13:07:38')
the values is part of a longer string variable. Basically I'm passing an array of records from a c# program into a stored proc...
regards
Davej
-
I think you're making this harder than necessary. You can just do this:
DECLARE @datetimestring varchar(20)
SET @datetimestring = '30/01/2006 13:07:38'
SET DATEFORMAT dmy
SELECT CONVERT(datetime, @datetimestring)
SET DATEFORMAT mdy -- Reset dateformat to original setting
-
Thanks a million!, made it a proc and all works fine. Sometime when you get so bogged down in code you miss the obvious things.
Cheers!
Davej
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|