Problem with dates

The 3rd party database I have been "gifted" stores dates as varchar(10)
formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
To ease use I am trying to write a function that will join the two
fields and return it as a single smalldatetime field.
The gist of the function is below
[code]
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
DECLARE @.l_str_date varchar(19)
SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
@.i_str_time
SELECT @.l_dte_return = @.l_str_date
SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
print @.l_dte_return
[/code]
This function always returns the return value as "Aug 9 2005 10:05AM"
with SQL if I do a print. No matter what I specify in the final
convert statement. If I put this into the function and pull it back it
returns "2005-09-08 10:05:00"
Any suggestions please.
Thanks> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print.
Print returns a string. Someone in the SQL Server dev team (as MS or more pr
obably Sybase) has
decided to format a datetime string in that way. SELECT, OROH, returns datet
ime data, where it is
the client that make it into a readable format.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123754445.406625.316790@.g44g2000cwa.googlegroups.com...
> The 3rd party database I have been "gifted" stores dates as varchar(10)
> formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
> To ease use I am trying to write a function that will join the two
> fields and return it as a single smalldatetime field.
> The gist of the function is below
> [code]
> DECLARE @.i_str_date varchar(10)
> DECLARE @.i_str_time varchar(8)
> SET @.i_str_date = '2005-09-08'
> SET @.i_str_time = '10:04:42'
> DECLARE @.l_dte_return smalldatetime
> DECLARE @.l_str_date varchar(19)
> SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
> SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
> @.i_str_time
> SELECT @.l_dte_return = @.l_str_date
> SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
> print @.l_dte_return
> [/code]
> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print. No matter what I specify in the final
> convert statement. If I put this into the function and pull it back it
> returns "2005-09-08 10:05:00"
> Any suggestions please.
> Thanks
>|||The code looks OK to me. PRINT performs an implict conversion to a
string using the default date conversion format - what matters is that
your function returns the correct datetime value. The reason that your
result is rounded to the nearest minute is because that's the precision
supported by SMALLDATETIME. If you need seconds then make the output
DATETIME and change the CONVERT function to DATETIME also.
David Portas
SQL Server MVP
--|||That explains the seconds rounding up thanks.
but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
format I have specified which should be dd/mm/yyyy hh:mm:ss
Any ideas?|||It returns datetime, which doesn't have a format. Datetime is formatted by t
he client application.
See http://www.karaszi.com/SQLServer/info_datetime.asp for more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123766631.480840.219740@.g47g2000cwa.googlegroups.com...
> That explains the seconds rounding up thanks.
> but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
> format I have specified which should be dd/mm/yyyy hh:mm:ss
> Any ideas?
>|||cheers very useful|||On 11 Aug 2005 03:00:45 -0700, graz79 wrote:
Hi graz79,
Your actual question has already been asnwered, I believe. But...
(snip)
>Any suggestions please.
>Thanks
Ask, and you shall be given...

>The gist of the function is below
>[code]
>DECLARE @.i_str_date varchar(10)
>DECLARE @.i_str_time varchar(8)
>SET @.i_str_date = '2005-09-08'
>SET @.i_str_time = '10:04:42'
>DECLARE @.l_dte_return smalldatetime
>DECLARE @.l_str_date varchar(19)
>SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
>SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
>@.i_str_time
This loads the date+time in the varchar column, but in this format:
"dd/mm/yyyy hh:mm:ss". That format is not safe for conversions.
Americans will think that your date is September 8th.

>SELECT @.l_dte_return = @.l_str_date
Here, you are doing an implicit conversion from varchar to
smalldatetime. It is completely dependent on the localization settings
what the result will be.

>SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
This converts from smalldatetime to smalldatetime. Too late to supply a
style parameter 103 now - if the conversion went wrong in the previous
statement, it won't be corrected here.

>To ease use I am trying to write a function that will join the two
>fields and return it as a single smalldatetime field.
User-defined functions can be slow. There's absolutely no need for a UDF
in this case. Since the date is already in the "yyyy-mm-dd" format and
the time is in the "hh:mm:ss" format, it's very easy to get to one of
the guaranteed safe and unambiguous formats: "yyyy-mm-ddThh:mm:ss":
-- Use variables to demonstrate the technique
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
-- This is where the actual work is done
SET @.l_dte_return = @.i_str_date + 'T' + @.i_str_time
-- Show results
PRINT @.l_dte_return
Instead of making a user-defined function and calling that, simply pop
the actual formula for the conversion "column1 + 'T' + column2" where
you need it in the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)