SET NOCOUNT ON
declare @.tab table(vc varchar(2000))
insert into @.tab select '10.1'
insert into @.tab select '10.1.1'
insert into @.tab select '10.1.2'
insert into @.tab select '10.1.1.1'
insert into @.tab select '10.1.1.1.1'
The problem is that i need to select only 10.1.1 and 10.1.2 when i put input
as 10.1
If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
I tried this but not working
SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'try this
SELECT * FROM @.Tab WHERE vc like '10.1.[^.%]'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"aneeshattingal" wrote:
> SET NOCOUNT ON
> declare @.tab table(vc varchar(2000))
> insert into @.tab select '10.1'
> insert into @.tab select '10.1.1'
> insert into @.tab select '10.1.2'
> insert into @.tab select '10.1.1.1'
> insert into @.tab select '10.1.1.1.1'
> The problem is that i need to select only 10.1.1 and 10.1.2 when i put inp
ut
> as 10.1
> If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
> I tried this but not working
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'
>
>|||aneeshattingal wrote:
> SET NOCOUNT ON
> declare @.tab table(vc varchar(2000))
> insert into @.tab select '10.1'
> insert into @.tab select '10.1.1'
> insert into @.tab select '10.1.2'
> insert into @.tab select '10.1.1.1'
> insert into @.tab select '10.1.1.1.1'
> The problem is that i need to select only 10.1.1 and 10.1.2 when i put inp
ut
> as 10.1
> If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
> I tried this but not working
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'
Drop the '%' from your query:
SELECT * FROM @.Tab WHERE vc like '10.1.[^.]'|||Sorry.. that won't work for 10.1.12.. Wrong solution.
You will have to do it the long way :)
SELECT * FROM @.Tab WHERE
vc not like '10.1.%[.]%'
and vc like '10.1.%'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Omnibuzz" wrote:
> try this
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.%]'
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "aneeshattingal" wrote:
>|||Thank guys , both worked ..
"aneeshattingal" <aneeshattingal@.hotmail.com> wrote in message
news:u0zMeYkjGHA.3408@.TK2MSFTNGP05.phx.gbl...
> SET NOCOUNT ON
> declare @.tab table(vc varchar(2000))
> insert into @.tab select '10.1'
> insert into @.tab select '10.1.1'
> insert into @.tab select '10.1.2'
> insert into @.tab select '10.1.1.1'
> insert into @.tab select '10.1.1.1.1'
> The problem is that i need to select only 10.1.1 and 10.1.2 when i put
> input as 10.1
> If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
> I tried this but not working
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'
>
Showing posts with label varchar. Show all posts
Showing posts with label varchar. Show all posts
Wednesday, March 28, 2012
problem with join
hi, I have two tables join with each other,
key from table1 is nvarchar column, key from table2 is varchar column, so
when they join, the performance is very bad, however, when I change both
them to varchar and join them together, the performance is much better.
but the problem is I don't want to change the column definition, I still
want to improve the query. is it possible?When column datatypes are different in comparison side, SQL Server has to
internally convert it for you and that's why your query is performing slower
.
This is by design and is the expected.
You can use CAST funtion and see if it helps which I highly doubt.
"Britney" wrote:
> hi, I have two tables join with each other,
> key from table1 is nvarchar column, key from table2 is varchar column, s
o
> when they join, the performance is very bad, however, when I change both
> them to varchar and join them together, the performance is much better.
> but the problem is I don't want to change the column definition, I still
> want to improve the query. is it possible?
>
>
key from table1 is nvarchar column, key from table2 is varchar column, so
when they join, the performance is very bad, however, when I change both
them to varchar and join them together, the performance is much better.
but the problem is I don't want to change the column definition, I still
want to improve the query. is it possible?When column datatypes are different in comparison side, SQL Server has to
internally convert it for you and that's why your query is performing slower
.
This is by design and is the expected.
You can use CAST funtion and see if it helps which I highly doubt.
"Britney" wrote:
> hi, I have two tables join with each other,
> key from table1 is nvarchar column, key from table2 is varchar column, s
o
> when they join, the performance is very bad, however, when I change both
> them to varchar and join them together, the performance is much better.
> but the problem is I don't want to change the column definition, I still
> want to improve the query. is it possible?
>
>
Friday, March 23, 2012
Problem with IN
I want to create a store procedure like this
CREATE PROCEDURE test
@.values VARCHAR(8000)
AS
SELECT *
FROM MyTable
WHERE MyTable.ID IN(@.values)
GO
the parameter @.values = '''1'',''3'',''5'',''6'',''7'''
How can I do that??
Thanks :p
Franky
franky@.boucheros.comTry writing the SELECT sentence an a string and then EXECUTE this string.
Originally posted by Franky
I want to create a store procedure like this
CREATE PROCEDURE test
@.values VARCHAR(8000)
AS
SELECT *
FROM MyTable
WHERE MyTable.ID IN(@.values)
GO
the parameter @.values = '''1'',''3'',''5'',''6'',''7'''
How can I do that??
Thanks :p
Franky
franky@.boucheros.com|||CREATE PROCEDURE test (@.values VARCHAR(8000))
as
Declare @.Query nVarchar(1000)
SET @.Query=N'SELECT * FROM Table ' +
'WHERE Table.ID IN( ' + @.values + ')'
EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35
====================================
Where @.values must be
@.values = '''1''' + ','+ '''3'''+ ',' + '''5''' + ','+'''6'''+','+'''7'''|||Lots of ways to skin this cat. A non-dynamic solution:
SELECT *
FROM MyTable
WHERE @.values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''
Another method would be to create a user-defined funtion that returns a table of values from your string.
blindman|||Originally posted by blindman
Lots of ways to skin this cat. A non-dynamic solution:
SELECT *
FROM MyTable
WHERE @.values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''
Another method would be to create a user-defined funtion that returns a table of values from your string.
blindman
Or this way
SELECT *
FROM MyTable
WHERE CHARINDEX(@.values,cast(MyTable.ID as varchar))>0|||Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").
blindman|||Originally posted by blindman
Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").
blindman
blindman - it was my fault but another one - it needs to change order of arguments in charindex function. Nothing is wrong with quotes. Check this one:
create table #test(id int identity,code varchar(10))
insert #test(code) values('a')
insert #test(code) values('b')
insert #test(code) values('c')
declare @.list varchar(80)
set @.list='''1'',''3'',''5'',''6'',''7'''
select * from #test
where CHARINDEX(cast(id as varchar),@.list)>0
CREATE PROCEDURE test
@.values VARCHAR(8000)
AS
SELECT *
FROM MyTable
WHERE MyTable.ID IN(@.values)
GO
the parameter @.values = '''1'',''3'',''5'',''6'',''7'''
How can I do that??
Thanks :p
Franky
franky@.boucheros.comTry writing the SELECT sentence an a string and then EXECUTE this string.
Originally posted by Franky
I want to create a store procedure like this
CREATE PROCEDURE test
@.values VARCHAR(8000)
AS
SELECT *
FROM MyTable
WHERE MyTable.ID IN(@.values)
GO
the parameter @.values = '''1'',''3'',''5'',''6'',''7'''
How can I do that??
Thanks :p
Franky
franky@.boucheros.com|||CREATE PROCEDURE test (@.values VARCHAR(8000))
as
Declare @.Query nVarchar(1000)
SET @.Query=N'SELECT * FROM Table ' +
'WHERE Table.ID IN( ' + @.values + ')'
EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35
====================================
Where @.values must be
@.values = '''1''' + ','+ '''3'''+ ',' + '''5''' + ','+'''6'''+','+'''7'''|||Lots of ways to skin this cat. A non-dynamic solution:
SELECT *
FROM MyTable
WHERE @.values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''
Another method would be to create a user-defined funtion that returns a table of values from your string.
blindman|||Originally posted by blindman
Lots of ways to skin this cat. A non-dynamic solution:
SELECT *
FROM MyTable
WHERE @.values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''
Another method would be to create a user-defined funtion that returns a table of values from your string.
blindman
Or this way
SELECT *
FROM MyTable
WHERE CHARINDEX(@.values,cast(MyTable.ID as varchar))>0|||Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").
blindman|||Originally posted by blindman
Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").
blindman
blindman - it was my fault but another one - it needs to change order of arguments in charindex function. Nothing is wrong with quotes. Check this one:
create table #test(id int identity,code varchar(10))
insert #test(code) values('a')
insert #test(code) values('b')
insert #test(code) values('c')
declare @.list varchar(80)
set @.list='''1'',''3'',''5'',''6'',''7'''
select * from #test
where CHARINDEX(cast(id as varchar),@.list)>0
Friday, March 9, 2012
Problem with Dynamic Where Clause
I have a stored proc that accepts a varchar as a parameter. What is being
passed in is one or more IDs in a comma separated list. (ie '123,567,789')
In the where clause I want to pull are records where a value is IN the list
being passed in. (ie. WHERE column IN (@.Var))
When I run this stored proc I get an error because its treating
'123,567,789' as one varchar value insstead of 3 int values.
Is there anything I can do to work around this?
Thanks in advance,
Mike RFaking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Mike" wrote:
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>|||Mike wrote:
> I have a stored proc that accepts a varchar as a parameter. What is
> being passed in is one or more IDs in a comma separated list. (ie
> '123,567,789') In the where clause I want to pull are records where a
> value is IN the list being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
You have to use dynamic SQL to do what you want and you'll have to grant
users select access on the tables in question if they don't already have
those rights. That might be asecurity risk in your environment.
declare @.n nvarchar(1000)
Set @.n = N'Select col1 from mytable where col2 in (' + @.Var + N')'
Exec sp_executesql @.n
The other option is to create a temp table with all those IDs and join
the temp table with the main table in the query.
David Gugick
Imceda Software
www.imceda.com|||In article <Op3sqtnJFHA.220@.TK2MSFTNGP10.phx.gbl>,
mraeNOSPAM@.NOSPAMATALLcalibrus.com says...
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>
http://www.sommarskog.se/arrays-in-...ist-of-integers
****************************************
************************
Tapio Kulmala
"Those are my principles. If you don't like them I have others."
- Groucho Marx
****************************************
************************|||http://www.aspfaq.com/2248
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike" <mraeNOSPAM@.NOSPAMATALLcalibrus.com> wrote in message
news:Op3sqtnJFHA.220@.TK2MSFTNGP10.phx.gbl...
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the
list
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>|||If you use the attached User Defined Function to convert the Delimited list
in =to a table variable, you can simply join your main query to this table
variable...
Here's the UDF
Create Function dbo.ParseString (
@.S VarChar(8000), @.delim Char(1))
Returns @.tOut Table
(ValNum Integer Primary Key Identity,
sVal VarChar(1000))
As
Begin
Declare @.sVal VarChar(1000)
Declare @.dPos Integer
Declare @.Start Integer Set @.Start = 1
-- --
If @.S = @.delim Or Len(@.S) = 0 Return
Else If Right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
-- --
Set @.dPos = CharIndex(@.delim, @.S, 1)
While @.dPos <> 0
Begin
Set @.sVal = LTrim(Substring(@.S, @.Start, @.dPos - @.Start))
Insert @.tOut (sVal) Values (@.sVal)
Set @.Start = @.dPos + 1
Set @.dPos = CharIndex(@.delim, @.S, @.Start)
End
Return
-- ---
End
And in your stoored Proc, just join to the output of this udf as though it
was a table, containing a varchar() named sVal...
Select <stuff>
From Table T
Join dbo.ParseString(@.Var, ',') as V
On T.ColumnName = Cast(V.sVal as Integer)
"Mike" wrote:
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>
passed in is one or more IDs in a comma separated list. (ie '123,567,789')
In the where clause I want to pull are records where a value is IN the list
being passed in. (ie. WHERE column IN (@.Var))
When I run this stored proc I get an error because its treating
'123,567,789' as one varchar value insstead of 3 int values.
Is there anything I can do to work around this?
Thanks in advance,
Mike RFaking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Mike" wrote:
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>|||Mike wrote:
> I have a stored proc that accepts a varchar as a parameter. What is
> being passed in is one or more IDs in a comma separated list. (ie
> '123,567,789') In the where clause I want to pull are records where a
> value is IN the list being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
You have to use dynamic SQL to do what you want and you'll have to grant
users select access on the tables in question if they don't already have
those rights. That might be asecurity risk in your environment.
declare @.n nvarchar(1000)
Set @.n = N'Select col1 from mytable where col2 in (' + @.Var + N')'
Exec sp_executesql @.n
The other option is to create a temp table with all those IDs and join
the temp table with the main table in the query.
David Gugick
Imceda Software
www.imceda.com|||In article <Op3sqtnJFHA.220@.TK2MSFTNGP10.phx.gbl>,
mraeNOSPAM@.NOSPAMATALLcalibrus.com says...
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>
http://www.sommarskog.se/arrays-in-...ist-of-integers
****************************************
************************
Tapio Kulmala
"Those are my principles. If you don't like them I have others."
- Groucho Marx
****************************************
************************|||http://www.aspfaq.com/2248
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike" <mraeNOSPAM@.NOSPAMATALLcalibrus.com> wrote in message
news:Op3sqtnJFHA.220@.TK2MSFTNGP10.phx.gbl...
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the
list
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>|||If you use the attached User Defined Function to convert the Delimited list
in =to a table variable, you can simply join your main query to this table
variable...
Here's the UDF
Create Function dbo.ParseString (
@.S VarChar(8000), @.delim Char(1))
Returns @.tOut Table
(ValNum Integer Primary Key Identity,
sVal VarChar(1000))
As
Begin
Declare @.sVal VarChar(1000)
Declare @.dPos Integer
Declare @.Start Integer Set @.Start = 1
-- --
If @.S = @.delim Or Len(@.S) = 0 Return
Else If Right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
-- --
Set @.dPos = CharIndex(@.delim, @.S, 1)
While @.dPos <> 0
Begin
Set @.sVal = LTrim(Substring(@.S, @.Start, @.dPos - @.Start))
Insert @.tOut (sVal) Values (@.sVal)
Set @.Start = @.dPos + 1
Set @.dPos = CharIndex(@.delim, @.S, @.Start)
End
Return
-- ---
End
And in your stoored Proc, just join to the output of this udf as though it
was a table, containing a varchar() named sVal...
Select <stuff>
From Table T
Join dbo.ParseString(@.Var, ',') as V
On T.ColumnName = Cast(V.sVal as Integer)
"Mike" wrote:
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>
Monday, February 20, 2012
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_datevarchar(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 probably Sybase) has
decided to format a datetime string in that way. SELECT, OROH, returns datetime 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.googlegr oups.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 the 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.googlegr oups.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_datevarchar(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)
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_datevarchar(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 probably Sybase) has
decided to format a datetime string in that way. SELECT, OROH, returns datetime 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.googlegr oups.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 the 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.googlegr oups.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_datevarchar(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)
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 probably Sybase) has
decided to format a datetime string in that way. SELECT, OROH, returns datetime 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 the 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)
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 probably Sybase) has
decided to format a datetime string in that way. SELECT, OROH, returns datetime 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 the 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)
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)
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)
problem with datepart ....
hello
i have a table with date entries of type string ...tries of type varchar li
ke
01.01.2006
.....
01.02.2006
.....
28.02.2006
print DATEPART(dw,'01.02.2006') ->2
print DATEPART(dw,'14.02.2006') ->
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
i think the problems are because on server are default german and the othe
are english.
I did not want to change all kind of scrips - Is the any possibility to add
a command in the first line to change this ...
best reagrds
what can i do?
print DATEPART(dw,'14.02.2006')Try:
print DATEPART(dw,convert (datetime, '14.02.2006', 104))
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
hello
i have a table with date entries of type string ...tries of type varchar
like
01.01.2006
.....
01.02.2006
.....
28.02.2006
print DATEPART(dw,'01.02.2006') ->2
print DATEPART(dw,'14.02.2006') ->
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
i think the problems are because on server are default german and the othe
are english.
I did not want to change all kind of scrips - Is the any possibility to add
a command in the first line to change this ...
best reagrds
what can i do?
print DATEPART(dw,'14.02.2006')|||try this
SET DATEFORMAT DMY
SELECT DATEPART(dw,'14.02.2006')
http://sqlservercode.blogspot.com/|||Xavier,
I suggest you look up (& make use of) the
SET DATEFORMAT
statement in Books Online.
Robert
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
> hello
> i have a table with date entries of type string ...tries of type varchar
> like
> 01.01.2006
> .....
> 01.02.2006
> .....
> 28.02.2006
>
> print DATEPART(dw,'01.02.2006') ->2
> print DATEPART(dw,'14.02.2006') ->
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> i think the problems are because on server are default german and the othe
> are english.
> I did not want to change all kind of scrips - Is the any possibility to
> add
> a command in the first line to change this ...
> best reagrds
> what can i do?
> print DATEPART(dw,'14.02.2006')
>|||Try using CONVERT with the proper format.
print DATEPART(dw,convert(datetime, '14.02.2006', 104))|||thanks
"Tom Moreau" wrote:
> Try:
> print DATEPART(dw,convert (datetime, '14.02.2006', 104))
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
> hello
> i have a table with date entries of type string ...tries of type varchar
> like
> 01.01.2006
> ......
> 01.02.2006
> ......
> 28.02.2006
>
> print DATEPART(dw,'01.02.2006') ->2
> print DATEPART(dw,'14.02.2006') ->
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> i think the problems are because on server are default german and the othe
> are english.
> I did not want to change all kind of scrips - Is the any possibility to ad
d
> a command in the first line to change this ...
> best reagrds
> what can i do?
> print DATEPART(dw,'14.02.2006')
>
>|||thanks
i have a table with date entries of type string ...tries of type varchar li
ke
01.01.2006
.....
01.02.2006
.....
28.02.2006
print DATEPART(dw,'01.02.2006') ->2
print DATEPART(dw,'14.02.2006') ->
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
i think the problems are because on server are default german and the othe
are english.
I did not want to change all kind of scrips - Is the any possibility to add
a command in the first line to change this ...
best reagrds
what can i do?
print DATEPART(dw,'14.02.2006')Try:
print DATEPART(dw,convert (datetime, '14.02.2006', 104))
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
hello
i have a table with date entries of type string ...tries of type varchar
like
01.01.2006
.....
01.02.2006
.....
28.02.2006
print DATEPART(dw,'01.02.2006') ->2
print DATEPART(dw,'14.02.2006') ->
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
i think the problems are because on server are default german and the othe
are english.
I did not want to change all kind of scrips - Is the any possibility to add
a command in the first line to change this ...
best reagrds
what can i do?
print DATEPART(dw,'14.02.2006')|||try this
SET DATEFORMAT DMY
SELECT DATEPART(dw,'14.02.2006')
http://sqlservercode.blogspot.com/|||Xavier,
I suggest you look up (& make use of) the
SET DATEFORMAT
statement in Books Online.
Robert
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
> hello
> i have a table with date entries of type string ...tries of type varchar
> like
> 01.01.2006
> .....
> 01.02.2006
> .....
> 28.02.2006
>
> print DATEPART(dw,'01.02.2006') ->2
> print DATEPART(dw,'14.02.2006') ->
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> i think the problems are because on server are default german and the othe
> are english.
> I did not want to change all kind of scrips - Is the any possibility to
> add
> a command in the first line to change this ...
> best reagrds
> what can i do?
> print DATEPART(dw,'14.02.2006')
>|||Try using CONVERT with the proper format.
print DATEPART(dw,convert(datetime, '14.02.2006', 104))|||thanks
"Tom Moreau" wrote:
> Try:
> print DATEPART(dw,convert (datetime, '14.02.2006', 104))
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
> hello
> i have a table with date entries of type string ...tries of type varchar
> like
> 01.01.2006
> ......
> 01.02.2006
> ......
> 28.02.2006
>
> print DATEPART(dw,'01.02.2006') ->2
> print DATEPART(dw,'14.02.2006') ->
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> i think the problems are because on server are default german and the othe
> are english.
> I did not want to change all kind of scrips - Is the any possibility to ad
d
> a command in the first line to change this ...
> best reagrds
> what can i do?
> print DATEPART(dw,'14.02.2006')
>
>|||thanks
Subscribe to:
Posts (Atom)