Results 1 to 5 of 5

Thread: Date & time problems

Hybrid View

  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Unhappy 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

  2. #2
    Join Date
    Dec 2004
    Posts
    502
    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.

  3. #3
    Join Date
    Dec 2003
    Posts
    14
    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

  4. #4
    Join Date
    Dec 2004
    Posts
    502
    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

  5. #5
    Join Date
    Dec 2003
    Posts
    14

    Thumbs up

    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
  •