Converting Data

Hi
I am converting data from old DB to NEW DB
In the OLD table fields like "PhoneNumber" the data enterd are [ 657 985-986, (03)-987-543, 675(89)00, ect]
Is their any function in sql where I can get rid of all those spaces and () and - between the numbers as my new field is only numbers and with out space
Otherwise I have to clean them up manually as I have 1000000 records

cheers

hi koese,

as far as i know there's no direct function to help you out during migration or converting. an alternative i think of is first you let your column type to be varchar in new db.

then after migrating run a update i know it will take time but that will definately work.

then you can use a command likeSELECT REPLACE('abcdefghicde','cde','xxx')

thanks,

satish.

sqlsql

Converting a SQLite database to a SQL Server database

Hi

I have a SQLite database. I want to convert it to SQL Sever 2005 database. Can u pls guide me how to do it?

Imalka

Moved to SQL Server forum.

Converting a SQLite database to a SQL Server 2005 databse

Hi

I have a SQLite database. I want to convert it to a SQL Server 2005 database. Can u guide me how to do it?

Imalka

you mean to say you have a backup of litspeed (.lsb). In that case restore the lsb backup and you have the database. I am assuming that your lsb is a 2005 db.

Converting a Oracle PL/SQL command into MS SQL

hi

I was wondering if anyone would be able to advise on converting oracle PL/SQL features into MSSQL.

For example i have the following sequence + trigger below but cant find any information on creating a sequence in MSSQL, is it possible?

create sequence a_SEQ
start with 001
increment by 1

create or replace trigger a_TG
before insert a
for each row
begin
select (concat('D',(cast(a_SEQ.nextval as varchar(4))))) into :new.a_id from dual;
end;

any links or tutorials would be great, i've got a couple of MSSQL 2005 books which do explain triggers but examples are really needed to understand the full functionality.

cheersAs it was for SQL 2000, the SQL 2005 Books Online (BOL) is for me the quickest way to research something.

The Oracle squence is one of the things I miss in SQL Server. The thing that comes close is an IDENTITY column. In your example you seem to generate an unique number for a table. So define a_id as an IDENTITY column (see CREATE TABLE in the BOL) and forget about the trigger.

Note, after rereading this, the IDENTITY column works better/simpler than a sequence :)|||hi there

yes that identity column works great and only takes a second through the GUI.

Just with oracle we were taught to insert a letter before the unique ID to help identify the tables more, (this was done using a sequence + trigger).

for example on a table called detective instead of:
ID fname sname
1 bil fish
2 fred frog
3 dave dog

It would display:
ID fname sname
D1 bil fish
D2 fred frog
D3 dave dog

This would help identify that the ID was coming from the detective table.

Do you know a way of doing something along these lines with MS SQL.

cheers|||I would say don't do it.

You know what table it's in and what column it's in

Personally I would avoid surrogate keys|||OK

cheers for the advise|||Personally I would avoid surrogate keys
Sputter...choke...cough...
...but anyway, PROPER use of surrogate keys would not require adding prefixes to indicate their location. That should be discouraged. A surrogate key should have no inherent relationship to the data it identifies.

Converted Access database gives problems due to field names

Hi

I have been working with asp.net against an Access database (created by someone else several years ago). Now I have to move the database to run under MSSQL Server. I successfully updated it via the Access migration tool, and it's running fine under MSDE.

However, many of the fields in one table are 'named' as 'numbers' - eg 101, 102, 34 etc. Therefore SQL commands such as:

update Mytable set 101 = "test" where ID = 2

fail with Incorrect syntax near '101'.

I would never set up such a database structure myself, but I am stuck with it. Is there any way to make MSSQL commands work with this sort of naming?

Thanks for any help you can give.

LeeYou could try:

update Mytable set [101] = "test" where ID = 2

I have not tried this with fields that are all numbers, but I expect it should work.sqlsql

Convert varchar to datetime!

Hi:

I have a column call Date_Sent (28/02/2004)(dd/mm/yyyy) format as varchar at beginning. I want to convert to other column as datetime.

I use Query like:

SELECT

CAST(SUBSTRING(Date_Sent,1,2)as int) + '/' +
CAST(SUBSTRING(date_sent,4,2) as int) + '/'+CAST(SUBSTRING(DATE_SENT,7,4) as int)

From MyTable

It is not working, anybody can give me some advise!

thanks!

DECLARE @.Date_Sent CHAR(10)
SET @.Date_Sent = '28/02/2004'
SELECT CONVERT(DATETIME, @.Date_Sent, 103)|||

Try:

SELECT

Cast(SUBSTRING(Date_Sent,4,2) + '/' +
SUBSTRING(date_sent,1,2) + '/'+SUBSTRING(DATE_SENT,7,4) as datetime)

|||

The solution fromPDraigh worked for me .

Thanks

really should have designed database to use datetime in first place , but this is a nice work around

|||

Strange !! it gave me an error in the SQL statement !!

my field is a varchar

Convert Varchar into Int

Hi

I have imported some varchar figures.

such as 0.050 (we'll call this Field)

I need to convert them into int to be used for calculations.

when I do

CAST(Field as int)

I get...

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '0.050' to a column of data type int.

Normally I dont get this error?

Anyone?

Quote:

Originally Posted by flickimp

Hi

I have imported some varchar figures.

such as 0.050 (we'll call this Field)

I need to convert them into int to be used for calculations.

when I do

CAST(Field as int)

I get...

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '0.050' to a column of data type int.

Normally I dont get this error?

Anyone?

INT datatype (SQL Server Books online (BOL)

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

therefore... cast(field as decimal(18,3))
or
cast(field as numeric(18,3))

will return

.050

INT will round to the nearest whole number.

If calculating with decimalised data, numeric data precison and scale will demand decimal or numeric datatype

cast in itself cannot explicitly cast a 0.050 'varchar' value to a whole number
therefore inserting any 0.050 value in an integer would round it down to zero (the nearest whole number)


Regards

Jim :)

Convert to lower

hi

I have a column in my database i would like to convert to lowercase

is their a t-sql statement or something i can use so i dont have to do it manually ??

cheers!!!

Hi,

you can use theLOWER function in T-SQL.

Grz, Kris.

|||

hotsheep:

hi

I have a column in my database i would like to convert to lowercase

is their a t-sql statement or something i can use so i dont have to do it manually ??

cheers!!!

update table_name set column_name=lower(column_name) .. it will convert all ur records in column_name fields ............... to lower case

hope it will help u ...

convert tables in a database to use unicode

Hi!
I have to convert all nonUnicode-fields in a database to use unicode. I
tried to convert one column and realized that it will take very long time or
the memory usage will be too high.
If I create a database (a copy of the one that is used) and create tables
that use unicode in this one and then export the data from the old db to the
new db, is this a good idea? Are there any better ways ( there always are
:-), anyone who has more experience than me?)
Thanks for help!
//Malinthis is how I would do it.
script out all the objects into individual scripts.
create a vb script to search and replace varchar with nvarchar, char with
nchar and text with ntext.
build a database using the scripts.
run a comparison and synchronization on using the newly created database as
the source and the target which would be a copy of the entended database and
record the delta script.
Job done using DB Ghost.
Although the above is certainly possible it lacks any change management. If
you have all your source code in source control the changes could be
automatically made by checking out all the source and running the procedures
above. You'd then have a history of what is changing with your database code
via all the functions of your source control - such as who changed this? why
was it changed? when was it changed? how was it changed? where was it change
d?
most people use source control for procedural code - why not database code?
DB Ghost gives you a fast, easy way to manage your database code using your
favorite source control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Malin Davidsson" wrote:

> Hi!
> I have to convert all nonUnicode-fields in a database to use unicode. I
> tried to convert one column and realized that it will take very long time
or
> the memory usage will be too high.
> If I create a database (a copy of the one that is used) and create tables
> that use unicode in this one and then export the data from the old db to t
he
> new db, is this a good idea? Are there any better ways ( there always are
> :-), anyone who has more experience than me?)
> Thanks for help!
> //Malin
>
>|||Hmm... that's a very complex solution to a very simple problem.
Here's a much simpler way of achieving this goal:
1. Backup your DB if possible.
2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
Here, the script must take into account the size of the table row, to ensure
that you do not exceed 8060 bytes. This is quite simple to do though.
Run this script to loop over all DB tables.
Question: Do you also need to convert TEXT fields to NTEXT, or only from
VARCHAR to NVARCHAR?
I can help out with the script, if you need assistance.
Omri.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com|||my main point in doing it this way is to have all changes under source
control...
"Omri Bahat" wrote:

> Hmm... that's a very complex solution to a very simple problem.
> Here's a much simpler way of achieving this goal:
> 1. Backup your DB if possible.
> 2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
> Here, the script must take into account the size of the table row, to ensu
re
> that you do not exceed 8060 bytes. This is quite simple to do though.
> Run this script to loop over all DB tables.
> Question: Do you also need to convert TEXT fields to NTEXT, or only from
> VARCHAR to NVARCHAR?
> I can help out with the script, if you need assistance.
> Omri.
> --
> Omri Bahat
> SQL Farms Solutions
> www.sqlfarms.com
>|||Hi!
Yes I also want to change text to ntext, how do I do that? I tried alter
table but noticed tha I'm not allowed to change text-columns... :-/
thanks for all help!
//Malin
"Omri Bahat" <OmriBahat@.discussions.microsoft.com> wrote in message
news:06EAC641-ECFD-4E77-B8FE-01E5D8A83326@.microsoft.com...
> Hmm... that's a very complex solution to a very simple problem.
> Here's a much simpler way of achieving this goal:
> 1. Backup your DB if possible.
> 2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
> Here, the script must take into account the size of the table row, to
> ensure
> that you do not exceed 8060 bytes. This is quite simple to do though.
> Run this script to loop over all DB tables.
> Question: Do you also need to convert TEXT fields to NTEXT, or only from
> VARCHAR to NVARCHAR?
> I can help out with the script, if you need assistance.
> Omri.
> --
> Omri Bahat
> SQL Farms Solutions
> www.sqlfarms.com
>

convert tables in a database to use unicode

Hi!
I have to convert all nonUnicode-fields in a database to use unicode. I
tried to convert one column and realized that it will take very long time or
the memory usage will be too high.
If I create a database (a copy of the one that is used) and create tables
that use unicode in this one and then export the data from the old db to the
new db, is this a good idea? Are there any better ways ( there always are
:-), anyone who has more experience than me?)
Thanks for help!
//Malin
this is how I would do it.
script out all the objects into individual scripts.
create a vb script to search and replace varchar with nvarchar, char with
nchar and text with ntext.
build a database using the scripts.
run a comparison and synchronization on using the newly created database as
the source and the target which would be a copy of the entended database and
record the delta script.
Job done using DB Ghost.
Although the above is certainly possible it lacks any change management. If
you have all your source code in source control the changes could be
automatically made by checking out all the source and running the procedures
above. You'd then have a history of what is changing with your database code
via all the functions of your source control - such as who changed this? why
was it changed? when was it changed? how was it changed? where was it changed?
most people use source control for procedural code - why not database code?
DB Ghost gives you a fast, easy way to manage your database code using your
favorite source control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Malin Davidsson" wrote:

> Hi!
> I have to convert all nonUnicode-fields in a database to use unicode. I
> tried to convert one column and realized that it will take very long time or
> the memory usage will be too high.
> If I create a database (a copy of the one that is used) and create tables
> that use unicode in this one and then export the data from the old db to the
> new db, is this a good idea? Are there any better ways ( there always are
> :-), anyone who has more experience than me?)
> Thanks for help!
> //Malin
>
>
|||Hmm... that's a very complex solution to a very simple problem.
Here's a much simpler way of achieving this goal:
1. Backup your DB if possible.
2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
Here, the script must take into account the size of the table row, to ensure
that you do not exceed 8060 bytes. This is quite simple to do though.
Run this script to loop over all DB tables.
Question: Do you also need to convert TEXT fields to NTEXT, or only from
VARCHAR to NVARCHAR?
I can help out with the script, if you need assistance.
Omri.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com
|||my main point in doing it this way is to have all changes under source
control...
"Omri Bahat" wrote:

> Hmm... that's a very complex solution to a very simple problem.
> Here's a much simpler way of achieving this goal:
> 1. Backup your DB if possible.
> 2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
> Here, the script must take into account the size of the table row, to ensure
> that you do not exceed 8060 bytes. This is quite simple to do though.
> Run this script to loop over all DB tables.
> Question: Do you also need to convert TEXT fields to NTEXT, or only from
> VARCHAR to NVARCHAR?
> I can help out with the script, if you need assistance.
> Omri.
> --
> Omri Bahat
> SQL Farms Solutions
> www.sqlfarms.com
>
|||Hi!
Yes I also want to change text to ntext, how do I do that? I tried alter
table but noticed tha I'm not allowed to change text-columns... :-/
thanks for all help!
//Malin
"Omri Bahat" <OmriBahat@.discussions.microsoft.com> wrote in message
news:06EAC641-ECFD-4E77-B8FE-01E5D8A83326@.microsoft.com...
> Hmm... that's a very complex solution to a very simple problem.
> Here's a much simpler way of achieving this goal:
> 1. Backup your DB if possible.
> 2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
> Here, the script must take into account the size of the table row, to
> ensure
> that you do not exceed 8060 bytes. This is quite simple to do though.
> Run this script to loop over all DB tables.
> Question: Do you also need to convert TEXT fields to NTEXT, or only from
> VARCHAR to NVARCHAR?
> I can help out with the script, if you need assistance.
> Omri.
> --
> Omri Bahat
> SQL Farms Solutions
> www.sqlfarms.com
>

Convert System tables to User table in SQL 2000

Hi
I have some tables created as system tables by mistake. How do I
convert them back to user tables.
Thanks
HP
I'm not too sure how you could have done this 'by mistake', but please take
a look at this http://www.transactsql.com/html/sp_M...temobject.html
to switch objects back.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul Ibison wrote:
> I'm not too sure how you could have done this 'by mistake', but please take
> a look at this http://www.transactsql.com/html/sp_M...temobject.html
> to switch objects back.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
It is test server and somebody has messed up something that was causing
all object created as system object. I ran the script this morning to
switch but tables are created in last 2 days with this config are still
showing as system tables.
Thanks
Hp
|||HP wrote:
> Paul Ibison wrote:
>
> It is test server and somebody has messed up something that was causing
> all object created as system object. I ran the script this morning to
> switch but tables are created in last 2 days with this config are still
> showing as system tables.
> Thanks
> Hp
Hi Paul,
I looked the link you suggested. I want to do opposite to that script
Thanks
Hp
|||Have a look at the text in the procedure: sp_helptext
'sp_MS_marksystemobject'.
You'll need to do the reverse to unflag the tables. You might want to have a
PSS engineer work through this with you as it's a pretty unsupported method.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Convert System tables to User table in SQL 2000

Hi
I have some tables created as system tables by mistake. How do I
convert them back to user tables.
Thanks
HPI'm not too sure how you could have done this 'by mistake', but please take
a look at this http://www.transactsql.com/html/sp_...stemobject.html
to switch objects back.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Paul Ibison wrote:
> I'm not too sure how you could have done this 'by mistake', but please tak
e
> a look at this http://www.transactsql.com/html/sp_...stemobject.html
> to switch objects back.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
It is test server and somebody has messed up something that was causing
all object created as system object. I ran the script this morning to
switch but tables are created in last 2 days with this config are still
showing as system tables.
Thanks
Hp|||HP wrote:
> Paul Ibison wrote:
>
> It is test server and somebody has messed up something that was causing
> all object created as system object. I ran the script this morning to
> switch but tables are created in last 2 days with this config are still
> showing as system tables.
> Thanks
> Hp
Hi Paul,
I looked the link you suggested. I want to do opposite to that script
Thanks
Hp|||Have a look at the text in the procedure: sp_helptext
'sp_MS_marksystemobject'.
You'll need to do the reverse to unflag the tables. You might want to have a
PSS engineer work through this with you as it's a pretty unsupported method.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Convert sql server 2000 database to sql express 2005

Hi;

I have a database in sql server 2000 and i want to convert it to sql express 2005, so please tell me how I can do this. Thanks

Adnan

I havent worked with 2005 Express but couldnt you back up the 2000 copy and restore in 2005?

|||

Hi adnan152,

The first question I want to know is that do you have SQL Management Studio Express installed on your machine? Well, if not you can download it by clicking this link:http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

And after you have installed management studio express, you can refer to the steps listed below(NOTE HERE that the following article is for SQL Server 2005, not express version, so maybe you cannot follow it 100% exactly. But since they are almost the same, so,, just FYI. I strongly suggest you taking it as a reference )

Moving Data from SQL Server 2000 to SQL Server 2005
(In order to detach a database, expand theServer node in SQL 2000 Server to reveal all the databases. Right click on the database you want to detach, in this case Northwind, and choose All Tasks. From drop-down menu click on Detach Database, as shown in Figure 1 below. Once you detach a database, that node disappears, but the related *.mdf and *.ndf remain where they are.

The Detach Database window ..........

More detailed informatin, please take a look at:

http://www.aspfree.com/c/a/MS-SQL-Server/Moving-Data-from-SQL-Server-2000-to-SQL-Server-2005/1/

Also I want to verify if you also want to make the transformed database existing in App_Data folder. Just like aspnetdb.mdf. If you do need it, tell us, we will further help you. Thanks.

--

don't forget to mark as answer if you find our post helps you

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

|||

Yes, i have management studio, and it worked.

Thank you.

Adnan

Convert Number to Hours and Minutes format

Hi

I am new to Crystal Reports 10 and I hope that somebody can help me with this.

Basically I am trying to convert a number to hours and minutes format.

What the guys here do is enter the time spent doing a particular job into Heat. This is not in time format, but simply a number. This can be 20, or 30 or whatever. To Heat, all it is is a number but we all know it means minutes.
The field is known as {Calllog.TimeSpent}

I then run a report that displays all calls logged for a particular group with the sum of {Calllog.TimeSpent} at the end to give the Total Time Spent on that group. However this number ends up being something like 2545. I could just divide by 60, but the rounding doesn't work the way I would like.

Is there a way that Crystal reports can take the sum of this field and format so that I get this kind of result:

The sum of the field is: 245
Formatted sum of field: 4 Hrs, 5 Mins

Thanks a lot
PhilHi Phil,

Hope this helps...

You have an object that returns a numeric value for minutes. You want to convert this into hours and minutes in a BusinessObjects report. For example, "243 Minutes" should become 4 hours and 3 minutes (4.03).

Resolution
*****CONFIGURATION******

BusinessObject version 4.1.x and 5.x

**********RESOLUTION******

To convert an object called <Original Value> that returns the value 243 to 4.03 follow the steps below:

1. Create a new variable called <Original Value /100> with the formula:

=<Original Value>/100

This will return the value 2.43 in our example.

2. Create a new variable called <Original Value /100 & 0.60> with the formula:

=<Original Value /100>/0.60.

This will return the value 4.05.

3. Create a new variable called <Truncated Div/0.60 (=Hours)> with the formula:

=Truncate(<Original Value /100 & 0.60> ,0).

This will return the value 4.00 and will be the Hour value at the end.

4. Create a variable called <Truncated Value * 0.60>. with the formula:

=<Truncated Div/0.60 (=Hours)>*0.60

This will return the value 2.40.

5. Create a variable called <Remainder (=Minutes)> with formula:

=<Original Value /100>-<Truncated Value * 0.60>

This will return the value 0.03.

6. Set up the entire calculation by creating a new variable called <Time Calculation> with the formula:

=<Truncated Div/0.60 (=Hours)>+<Remainder (=Minutes)>

This will give the correct conversion of 243 to 4.03 and will successfully convert any numeric Minutes value into the correct Hours/Minutes format.|||Seems like a lot of effort :)

numbervar a := 245;
numbervar hours := truncate(a / 60);
numbervar mins := a mod 60;
totext(hours, 0, '') + ' hours, ' + totext(mins, 0, '') + ' mins'|||Thank you very much guys, both methods worked perfectly when tweaked to suit my report.

I'm slowly starting to get my head around it all.

Thanks again!!!

Phil

Convert DT_14 to DT_18

Hi!
I have a Fact_data flow with several Lookups.
In one there's OLE Db source DT_14 (persons id's) which need to be compared to a column DT_18 from Dim_salesperson. Thats how I could collect the ID's from Dim_salesperson into OLE DB Destination. How could I compare, DT_14 data with DT_18?
First converting DT_14 to character. Secondly char to DT_18?
I would be so grateful if someone could give an idea...

curiousss wrote:

First converting DT_14 to character. Secondly char to DT_18?

Assuming you are talking about DT_I4 and DT_I8; that approach sounds reasonable. Have you try it?

|||Thanks, problem solved!

Convert Date and order by

Hi!

I have a little problem. I'm trying to sort a date I have converted like this
Convert(datetime,LH.LoginDateTime,103) as RegistrationDate

But when I use Order by on RegistrationDate it only sort on days:

01/11/2006
01/12/2006
02/11/2006
02/12/2006
03/11/2006
03/12/2006

I'll guess it's because of the "varchar" convert, but I need the date to bee inn this format, since I only shall check the date and not the time. Is there a way around this, so I can order it like this? (Se under)

01/11/2006
02/11/2006
03/11/2006
01/12/2006
02/12/2006
03/12/2006

Sample SQL;

select Convert(varchar,LH.LoginDateTime,103) as RegistrationDate,
select count(*) from LoginHistory AS LH2 where datepart(hh,LH2.LoginDateTime)<7 AND
Convert(varchar,LH2.LoginDateTime,103)>=Convert(varchar,LH.LoginDateTime,103) AND
Convert(varchar,LH2.LoginDateTime,103)<=Convert(varchar,LH.LoginDateTime,103))
As beforehour07
from LoginHistory AS LH
where LH.LoginDateTime >='''+ Convert(varchar,@.FromDate,113) + ''' ' +
'and LH.LoginDateTime <='''+ Convert(varchar,@.ToDate,113) + ''' ' +
'group by Convert(varchar,LH.LoginDateTime,103)'
Order by RegistrationDate

Hi there,

Why dont you just order by the LH.LoginDateTime field?

thanks,

Murthy here

|||

You should also look on your count select statement because it will not work correctly comparing strings not dates, try this if you would like to use days only without time:

select Convert(varchar,LH.LoginDateTime,103) as RegistrationDate,
(select count(*) from LoginHistory AS LH2 where datepart(hh,LH2.LoginDateTime)<7 AND
convert(datetime,Convert(varchar,LH2.LoginDateTime,103))>=convert(datetime,Convert(varchar,LH.LoginDateTime,103)) AND
convert(datetime,Convert(varchar,LH2.LoginDateTime,103))<=convert(datetime,Convert(varchar,LH.LoginDateTime,103)))
As beforehour07
from LoginHistory AS LH
where LH.LoginDateTime >='''+ Convert(varchar,@.FromDate,113) + ''' ' +
'and LH.LoginDateTime <='''+ Convert(varchar,@.ToDate,113) + ''' ' +
'group by Convert(varchar,LH.LoginDateTime,103)'
Order by convert(datetime,RegistrationDate)

or you can try to rewrite your query to work much faster with single join instead of multiple internal queries to do counts.

This one will kill your server if you will try to process table with thousands of records

|||Thanks for the answer!

If I order by LH.LoginDateTime, I have to select from LH.LoginDate and group by LH.LoginDate or else I get the error:
"Column "LoginDateTime" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause." since the?dates don't match anymore.

If I put LH.LoginDateTime in the select,group and Order by, the dates on the same day won't match so the group by won't hit. I will get a new row for every hit on the same day like this:

01.11.2006 00:11:40
01.11.2006 00:15:41
01.11.2006 00:15:44
01.11.2006 00:16:48

I need to convert the date to dd/mm/yyyy(or something like that, removing the time) and be able to group and then order it. Do you or anyone know if it's possible?

Murthy Puvvada:

Hi there,

Why dont you just order by the LH.LoginDateTime field?

thanks,

Murthy here?

|||Fixed it, I could use cast(floor(cast(LH.LoginDateTime as float)) as datetime) to sett the time to 00:00:00