Rearrange String

I have a database where dates are stored in the following format yyyymmdd. I want to write a query where this is broken up and returned in the format mm-dd-yyyy ... Is this possible in SQL server?try this

SELECT CONVERT (datetime,, <DateFIELD> , 110)
FROM <TABLE>

Or

SELECT SUBSTR(<DateFIELD>, 5, 2) + "-" + SUBSTR(<DateFIELD>, 7, 2) + "-" + SUBSTR(<DateFIELD>, 1, 4) AS YOurField
FROM <TABLE>

<DateFIELD> - The name of your date field|||if you want mm-dd-yyyy then

select convert(varchar,getdate(),110)
select substring('20030201',5,2) + '-' + right('20030201',2) + '-' + left('20030201',4)

substitute your db field for getdate() or '20030227'|||Pardon my ignorance, but just for future reference, what is the "110" about?|||CONVERT (data_type[(length)], expression [, style])

it is the date style

0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 or 101 USA mm/dd/yy
2 or 102 ANSI yy.mm.dd
3 or 103 British/French dd/mm/yy
4 or 104 German dd.mm.yy
5 or 105 Italian dd-mm-yy
6 or 106 - dd mon yy
7 or 107 - mon dd, yy
8 or 108 - hh:mm:ss
9 or 109 (*) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 or 110 USA mm-dd-yy
11 or 111 JAPAN yy/mm/dd
12 or 112 ISO yymmdd
-13 or 113 (*) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 or 114 - hh:mi:ss:mmm(24h)
20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)|||Look in bol under convert - it gives all the styles.
They affect converting to and from character format.|||Thanks guys... thats been really helpful... I have one other similar problem that ye might be able to help me with... In the same table I have a field for time and it's stored in the following format:

- 9:20am is stored as 920
- 3:30pm is stored as 1530
- 12:20am is stored as 20
- 12:05 is stored as 5

Do ye have any suggestion as to how I could call this back in the correct format?|||try:

if object_id('tempdb..#Tmp') is not null drop table #Tmp
create table #tmp(dt1 int, tm1 int,dt2 varchar(8), tm2 varchar(4))
insert into #tmp values(20030228,920,'20030228','920')
insert into #tmp values(20030228,1530,'20030228','1530')
insert into #tmp values(20030228,20,'20030228','20')
insert into #tmp values(20030228,5,'20030228','5')

select * From #tmp

select convert(varchar,cast(cast(dt1 as varchar) as datetime),110)
, substring(dt2,5,2) + '-' + right(dt2,2) + '-' + left(dt2,4)
from #tmp

select right(convert(varchar,cast('01-Jan-1753 ' +
left(right('0000' + cast(tm1 as varchar),4),2) +
':' +
right('0000' + cast(tm1 as varchar),2) as datetime)),7)
, right(convert(varchar,cast('01-Jan-1753 ' +
left(right('0000' + tm2,4),2) +
':' +
right('0000' + tm2,2) as datetime)),7)
from #tmp

and in anticipation of your next question...

select convert(varchar,cast(cast(dt1 as varchar) +
' ' +
left(right('0000' + cast(tm1 as varchar),4),2) +
':' +
right('0000' + cast(tm1 as varchar),2) as datetime), 100)
, convert(varchar,cast(substring(dt2,5,2) + '-' + right(dt2,2) + '-' + left(dt2,4) + ' ' +
left(right('0000' + cast(tm1 as varchar),4),2) +
':' +
right('0000' + cast(tm1 as varchar),2) as datetime), 100)
from #tmp|||Is there any easier way of doing this... this way seems very complicated !

Really complex query that has me stymied

I'm attempting to write a query that pulls data from two tables in a really
complex way, and I can't figure a way around it. Because of the architecture
in use, it has to be done in a single query, without using a stored
procedure. This is a simplified version of what I'm doing:
Let's say I have two tables, each with four columns, named A, B, C and D:
Table 1
A,B,C,D
1,2,3,5
3,5,7,9
3,9,2,6
3,6,7,1
9,8,3,0
2,5,8,1
Table 2
A,B,C,D
5,3,2,3
4,8,7,1
3,5,7,2
1,4,5,6
8,9,3,2
2,6,2,2
Each of the tables has thousands of records. I need a query that does this:
For each row in table 1, look up the values in table 2. If a row can be
found in table 2 whose columns A, B and C match columns A, B and C in table
1, and whose column D equals 2, then return the row from table 1. Do not
return a row unless all three columns A, B and C match, and Table2.D equals
2.
Using this rule on the table values above, it should be returning only the
row "3,5,7,9" from Table 1 because there is only one row in Table 2 that has
columns A,B,C,D equal to "3,5,7,2"
Here's what I'm trying now:
SELECT * FROM Table1
WHERE
Table1.A IN (SELECT DISTINCT(A) FROM Table2 WHERE Table2.D = 2)
AND
Table1.B IN (SELECT DISTINCT(B) FROM Table2 WHERE Table2.D = 2)
AND
Table1.C IN (SELECT DISTINCT(C) FROM Table2 WHERE Table2.D = 2)
This returns 3,5,7,9, 3,9,2,6 and 3,6,7,1 from Table 1, when it should only
be returning 3,5,7,9. It does this because column A matches (3), Column B
matches multiple records in Table 2 with a Table2.D=2, and so does Column C.
Even though I am specifying AND for the three columns, it is actually acting
as an OR, because each SELECT DISTINCT is separate, and does not take into
consideration the other SELECTs. The first SELECT DISTINCT pulls a result
set, and Column A is compared against it. The second SELECT DISTINCT pulls a
different result set, and Column B is compared against it. And so on. This
results in an additive comparison. What I need, in effect, is for the SELECT
DISTINCT(B) to be more like "Take the SELECT DISTINCT(A) result set and pare
it down further using the SELECT DISTINCT(B) values". Then The SELECT
DISTINCT(C) needs to be more like "Take the SELECT DISTINCT(B) result set
and pare it down even further using the SELECT DISTINCT(C) values".
I hope this makes sense, and that someone can see what I'm missing. I've
spent hours trying to figure out a way of doing this, without success.Scott,
Try this:
SELECT A, B, C, D
FROM [Table 1] AS T1
WHERE EXISTS (
SELECT * FROM [Table 2] AS T2
WHERE T2.A = T1.A
AND T2.B = T1.B
AND T2.C = T1.C
AND T2.D = 2
)
or
SELECT T1.A, T1.B, T1.C, T1.D
FROM [Table 1] AS T1
JOIN [Table 2] AS T2
ON T2.A = T1.A
AND T2.B = T1.B
AND T2.C = T1.C
WHERE
T2.D = 2
The latter query will return duplicate rows, so it could need
DISTINCT. Use whichever is faster (hopefully at least one
has no typos).
Steve Kass
Drew University
Scott MacLean wrote:

>I'm attempting to write a query that pulls data from two tables in a really
>complex way, and I can't figure a way around it. Because of the architectur
e
>in use, it has to be done in a single query, without using a stored
>procedure. This is a simplified version of what I'm doing:
>Let's say I have two tables, each with four columns, named A, B, C and D:
>Table 1
>A,B,C,D
>1,2,3,5
>3,5,7,9
>3,9,2,6
>3,6,7,1
>9,8,3,0
>2,5,8,1
>Table 2
>A,B,C,D
>5,3,2,3
>4,8,7,1
>3,5,7,2
>1,4,5,6
>8,9,3,2
>2,6,2,2
>Each of the tables has thousands of records. I need a query that does this:
>For each row in table 1, look up the values in table 2. If a row can be
>found in table 2 whose columns A, B and C match columns A, B and C in table
>1, and whose column D equals 2, then return the row from table 1. Do not
>return a row unless all three columns A, B and C match, and Table2.D equals
>2.
>Using this rule on the table values above, it should be returning only the
>row "3,5,7,9" from Table 1 because there is only one row in Table 2 that ha
s
>columns A,B,C,D equal to "3,5,7,2"
>Here's what I'm trying now:
>SELECT * FROM Table1
>WHERE
> Table1.A IN (SELECT DISTINCT(A) FROM Table2 WHERE Table2.D = 2)
>AND
> Table1.B IN (SELECT DISTINCT(B) FROM Table2 WHERE Table2.D = 2)
>AND
> Table1.C IN (SELECT DISTINCT(C) FROM Table2 WHERE Table2.D = 2)
>This returns 3,5,7,9, 3,9,2,6 and 3,6,7,1 from Table 1, when it should only
>be returning 3,5,7,9. It does this because column A matches (3), Column B
>matches multiple records in Table 2 with a Table2.D=2, and so does Column C
.
>Even though I am specifying AND for the three columns, it is actually actin
g
>as an OR, because each SELECT DISTINCT is separate, and does not take into
>consideration the other SELECTs. The first SELECT DISTINCT pulls a result
>set, and Column A is compared against it. The second SELECT DISTINCT pulls
a
>different result set, and Column B is compared against it. And so on. This
>results in an additive comparison. What I need, in effect, is for the SELEC
T
>DISTINCT(B) to be more like "Take the SELECT DISTINCT(A) result set and par
e
>it down further using the SELECT DISTINCT(B) values". Then The SELECT
>DISTINCT(C) needs to be more like "Take the SELECT DISTINCT(B) result set
>and pare it down even further using the SELECT DISTINCT(C) values".
>I hope this makes sense, and that someone can see what I'm missing. I've
>spent hours trying to figure out a way of doing this, without success.
>
>|||Hello, Scott
This should do the job:
SELECT * FROM Table1 T1
WHERE EXISTS (
SELECT * FROM Table2 T2
WHERE T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C
AND T2.D=2
)
Razvan|||Thanks Steve and Razvan, this did the trick. Once I saw this I thought; "of
COURSE" (hand slap on forehead).
Thanks again.
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1110347824.072514.140200@.z14g2000cwz.googlegroups.com...
> Hello, Scott
> This should do the job:
> SELECT * FROM Table1 T1
> WHERE EXISTS (
> SELECT * FROM Table2 T2
> WHERE T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C
> AND T2.D=2
> )
> Razvan
>

'Real only' Cells in SQL Server Management Studio / VS 2005

I have been using both SQL Server Management Studio and the Server Explorer of Visual Studio 2005 to populate my database tables as I write my application. Basically, I right-click on a table name and select "Open Table' in SMS and select 'View Table Data' in VS 2005. From there I normally just edit data as needed.

Today, I open up a particular table and one column of my table is made read-only, meaning I cannot update that cell in any records and I cannot add any new records. I can tell this column is read-only because the data is faded and 'Cell is Read only' is visible in the status pane under the results when I mouse-over that column. These read-only columns seem to pop-up almost at random. At work the other day a similar thing happened to anther table I was trying to modify.

It just so happens that the read-only column is the Primary Key of this particular table, but even when I remove the Primary Key designation I cannot edit data in this column. In addition, I have other tables with Primary Keys and I have no trouble updating data in those table columns.

This most certainly has me pulling my hair out and have been searching online for two days now trying to find the cause of this. Any help would be appreciated.

IMHO editing data directly to the table using the SSMS or VS has always downside for having issues with data commit. You will be able to control the transaction commit or rollback is the biggest advantage in using TSQL instead. THe behaviour you are getting is opening up in VS is considered as a view where updates are not allowed, fyi http://msdn2.microsoft.com/en-us/library/ms172022(VS.80).aspx and may be look at the properties to see update speicification to set it to UPDATE.

|||Thanks for the reply. According to this http://msdn2.microsoft.com/en-us/library/sb0bay6k(VS.80).aspx I'm doing it exactly as they explain. At this point I just want to insert a farking row and I don't care how I do it. I've tried to write straight queries and can't even get that to work. I've written command line queries for 4 years now so I know how to write SQL, but I've been screwing with this application for 2 days now just trying to insert a row, its beyond ridiculous. I still cannot find a single resource online that explains to my why I have an entire column that is read-only.

Someone for the love of God just tell me how to insert a row using this steaming pile of BS called Micrrosoft SQL Server Management Studio (or VS 2005). I have a table that looks like this:

StatID int (Primary Key)
SeasonCode char(4)
PlayerID int
StatCode char(3)
StatValue float

and all I want to do is insert a record. Someone please tell me how the fark to insert a row into this table. I don't care what it takes. The column StatID has its Identify Specification column set to 'Yes'.
|||

I had the exact same problem and found out what was causing it in my case.

I started with 'Open Table' which I then wanted to filter so I opened criteria pane. This showed '*' in the columns selected - fine, that's what I expected.

I then selected a specific column to create a filter but didn't bother to unclick the 'display' column and that column became Read Only in the results set.

When I unclicked the 'display' option for the filtered column, and executed it again, the column was editable.

I haven't had time to play around with this but it looks to me that SSMS cannot handle editing on a column that is displayed twice.

Don't ever remember having that problem in EM.

|||

When I unclicked the 'display' option for the filtered column

I meant the 'Output' option