Combining the dates

Hi Guys

I have got two columns- one is the year and the other is the month number. I need to combine these two columns so that they form a date

For Ex

Year Month CombinedColumn

2000 11 2000/11

2003 01 2003/01

I am using SQL Server 2005

Thanks

I'll assume that Year and Month are integers and CombinedColumn is of data type DATETIME. If other data types are used, this solution might not apply.

Code Block

CREATE TABLE #Temp

(

[Year] INT NOT NULL,

[Month] INT NOT NULL,

[CombinedCol] DATETIME NULL

)

INSERT INTO #Temp VALUES (2000, 11, NULL)

INSERT INTO #Temp VALUES (2003, 01, NULL)

UPDATE #Temp

SET [CombinedCol] = DATEADD(month, [Month] - 1, DATEADD(year, [Year] - 1900, '1900-01-01 00:00:00.000'))

SELECT * FROM #Temp

|||

you can try this..

select cast( (cast(yearValue as varchar(4))+cast(monthValue as varchar(2)) + '01') as datetime)

you will get the date as the first day of your year and month values...

|||

Harish has the right idea, but it should be noted that 2000/11 is not a valid date. You can just concatenate them for display, but as for making them a date, can you explain more what you will do with the dates once you have them concatenated? (unless just making them the first day of the month suffices, of course

|||

My query would be like select cast('20001101' as datetime) which sql server implicitly converts to datetime value from varchar if it is in yyyymmdd format. The user needs to decide which value he needs for the dd value in the string

|||

Here it is,

Code Block

Create Table #sampledata (

[Year] varchar(4),

[Month] varchar(2)

);

Insert Into #sampledata Values('2000','11');

Insert Into #sampledata Values('2003','01');

DECLARE @.UserDay varchar(2);

Set @.UserDay = '5'

Select Convert(Datetime, Year + Month + substring(cast((cast(@.UserDay as int) + 100) as varchar),2,2),112) [Output] from #sampledata

|||

Another method with fewer keystrokes

Code Block

SELECT CAST(LTRIM([Year] * 10000 + [Month] * 100 + @.UserDay) AS DATETIME)

FROM #sampledata

|||Is it bad to use implicit conversion? I believe it will be faster than explicitly handling the conversion.|||

Well, I think it is better to use explicit conversion. That way you leave no room for anyone for interpretation and avoid any ambiguities.

|||

Just a note: "Partial Dates" like "January, 1968" are not really supported. Basically in SQL, if you really want to work with dates, you must store a full date...."January 1, 1968" for example. SQL will infer the time component as being Midnight and store it as "01 Jan 1968 00:00" (if you're using smalldatetime) and "01 Jan 1968 00:00:00.000" using DateTime.

So, for your query, let's add a day-of-the-month component:

Code Block

select convert(smalldatetime,'01' + '/' + Month + '/' + Year) as ThisIsTheDate from

or...if the values are stored as numbers (which is not suggested in your example of "01" which would probably be "1" if it were stored as a number:

Code Block

select convert(smalldatetime,"01/" + right('00',convert(varchar,month),2) + '/' + convert(varchar,year))

from

|||

Thanks .It works fine

Cheers

|||

Actually, here's one that I like better:

Note that DateAdd(year,50,0) = 'January 1, 1950' and DateAdd(month, 5, 0) = 'June 1, 1900' so....

Code Block

CREATE TABLE #Temp

(

[Year] INT NOT NULL,

[Month] INT NOT NULL,

[CombinedCol] DATETIME NULL

)

INSERT INTO #Temp VALUES (2000, 11, NULL)

INSERT INTO #Temp VALUES (2003, 01, NULL)

select dateadd(month, [Month]-1,dateadd(Year,[year]-1900,0))

from #Temp

|||

I came up with the same approach, but after you. One little thing...rather than specify the "base date" with a string of "1900-01-01 00:00:00.000") you can simply use the numeric 0

Code Block

select DateAdd(month, 10, 0)

select DateAdd(month, 10, "1900-01-01 00:00:00.000")

combining many contains()

Hi all,
I have a nvarchar(255) column on a tblKeyword table with many queries in a
verified FTS form. i.e-
"Microsoft Corporation"
("Hewelet Packard") OR HP
Google OR Froogle
Sun
I want to issue a combined FTS query that will gather all (*) information
from the indexed table, based on all existing queries in the tblKeyword
table.
Ie, something like:
SELECT * FROM myftstable
WHERE CONTAINS(*,[("Microsoft Corporation") OR (("Hewelet Packard") OR HP)
OR (Google OR Froogle) OR (Sun)])
Is it possible to do it dynamiclly in some sort? other then connecting all
strings and sending them all to the contains?
Thanks!
Guy,
Yes, there is a way to do this via a stored proc:
use pubs
go
-- DROP PROCEDURE usp_FTSearchPubsInfo
CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
AS
declare @.s as varchar (8000)
set @.s='select pub_id, pr_info from pub_info where
contains(pr_info,'+''''+@.vcSearchText+''''+')'
exec (@.s)
go
-- returns 2 rows
EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
go
-- Using your example:
EXEC usp_FTSearchPubsInfo '("Microsoft Corporation" or ("Hewelet Packard" or
"HP") or ("Google" or "Froogle") or ("Sun"))'
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:Oz8p5UXKEHA.3728@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have a nvarchar(255) column on a tblKeyword table with many queries in a
> verified FTS form. i.e-
> "Microsoft Corporation"
> ("Hewelet Packard") OR HP
> Google OR Froogle
> Sun
> I want to issue a combined FTS query that will gather all (*) information
> from the indexed table, based on all existing queries in the tblKeyword
> table.
> Ie, something like:
> SELECT * FROM myftstable
> WHERE CONTAINS(*,[("Microsoft Corporation") OR (("Hewelet Packard") OR HP)
> OR (Google OR Froogle) OR (Sun)])
> Is it possible to do it dynamiclly in some sort? other then connecting all
> strings and sending them all to the contains?
> Thanks!
>
|||Hi John,
I meant how to do it programatically, so that the queries exists in
tblKeyword will be populated automatically as a long (@.vcSearchText varchar)
Guy
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23nWpPQYKEHA.1272@.tk2msftngp13.phx.gbl...
> Guy,
> Yes, there is a way to do this via a stored proc:
> use pubs
> go
> -- DROP PROCEDURE usp_FTSearchPubsInfo
> CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
> AS
> declare @.s as varchar (8000)
> set @.s='select pub_id, pr_info from pub_info where
> contains(pr_info,'+''''+@.vcSearchText+''''+')'
> exec (@.s)
> go
> -- returns 2 rows
> EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
> go
> -- Using your example:
> EXEC usp_FTSearchPubsInfo '("Microsoft Corporation" or ("Hewelet Packard"
or[vbcol=seagreen]
> "HP") or ("Google" or "Froogle") or ("Sun"))'
> Regards,
> John
>
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:Oz8p5UXKEHA.3728@.TK2MSFTNGP12.phx.gbl...
a[vbcol=seagreen]
information[vbcol=seagreen]
HP)[vbcol=seagreen]
all
>
|||Guy,
I'm not sure what you're asking for here... Could you provide some examples?
Are you looking for a client-side (IE-based) solution or a server-side
(T-SQL based) solution? If the former, you may want to checkout KB article
246800 (Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defaul...b;EN-US;246800
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:OMfprgdKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> I meant how to do it programatically, so that the queries exists in
> tblKeyword will be populated automatically as a long (@.vcSearchText
varchar)[vbcol=seagreen]
> Guy
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23nWpPQYKEHA.1272@.tk2msftngp13.phx.gbl...
Packard"[vbcol=seagreen]
> or
in[vbcol=seagreen]
> a
> information
tblKeyword
> HP)
> all
>
|||John hi,
I need a server-side solution (T-SQL based) for connecting all of the
records appear in tblKeyword into 1 long string. Is it possible?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OJ%238ruhKEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Guy,
> I'm not sure what you're asking for here... Could you provide some
examples?
> Are you looking for a client-side (IE-based) solution or a server-side
> (T-SQL based) solution? If the former, you may want to checkout KB article
> 246800 (Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries"
at:[vbcol=seagreen]
> http://support.microsoft.com//defaul...b;EN-US;246800
> Regards,
> John
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:OMfprgdKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> varchar)
> Packard"
queries[vbcol=seagreen]
> in
> tblKeyword
OR[vbcol=seagreen]
connecting
>
|||Hi Guy,
Ok. I looked back over your original posting and I didn't realize that you
had two tables - tblKeyword and myftstable - and that in affect you wanted
to "pass" the tblKeyword table values to CONTAINS search_condition clause.
I'm sure there are other ways of doing this, but for now, I've developed two
cursor based solutions - assuming I'm understanding your question properly:
The below examples use the database (pubs) and the FT-enabled table
(authors) and the table (keyword) is your tblKeyword table:
use pubs
go
create table keyword(kword varchar(50))
go
insert into keyword values ('white')
insert into keyword values ('("john" or "paul")')
insert into keyword values ('Yokomoto')
go
select * from keyword
go
-- Simple Cursor Fetch with a CONTAINS statement...
SET NOCOUNT ON
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
OPEN keyword_cursor
DECLARE @.keyword varchar(50)
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE (@.@.fetch_status <> -1)
BEGIN
select * from authors where contains(*,@.keyword)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
SET NOCOUNT OFF
GO
-- Complex Cursor Fetch into a temp table and then select from it...
set nocount on
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
CREATE TABLE #authors_PK (author_pk char(11))
OPEN keyword_cursor
DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
-- Fetch the first row in the cursor.
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.sql = 'insert into #authors_PK (author_pk) select au_id from
authors where contains(*, ''' + @.keyword + ''')'
exec(@.sql)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
select * from #authors_PK
drop table #authors_PK
go
-- Clean-up
drop table keyword
go
Let me know if this is what you're looking for.
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:urOPK8jKEHA.3492@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> John hi,
> I need a server-side solution (T-SQL based) for connecting all of the
> records appear in tblKeyword into 1 long string. Is it possible?
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OJ%238ruhKEHA.1340@.TK2MSFTNGP12.phx.gbl...
> examples?
article[vbcol=seagreen]
> at:
varchar(7800))[vbcol=seagreen]
> queries
Packard")
> OR
> connecting
>
|||Exactly!!
Thank you John!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uwMwhCnKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi Guy,
> Ok. I looked back over your original posting and I didn't realize that you
> had two tables - tblKeyword and myftstable - and that in affect you wanted
> to "pass" the tblKeyword table values to CONTAINS search_condition clause.
> I'm sure there are other ways of doing this, but for now, I've developed
two
> cursor based solutions - assuming I'm understanding your question
properly:[vbcol=seagreen]
> The below examples use the database (pubs) and the FT-enabled table
> (authors) and the table (keyword) is your tblKeyword table:
> use pubs
> go
> create table keyword(kword varchar(50))
> go
> insert into keyword values ('white')
> insert into keyword values ('("john" or "paul")')
> insert into keyword values ('Yokomoto')
> go
> select * from keyword
> go
> -- Simple Cursor Fetch with a CONTAINS statement...
> SET NOCOUNT ON
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select kword from keyword
> OPEN keyword_cursor
> DECLARE @.keyword varchar(50)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> select * from authors where contains(*,@.keyword)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> SET NOCOUNT OFF
> GO
> -- Complex Cursor Fetch into a temp table and then select from it...
> set nocount on
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select kword from keyword
> CREATE TABLE #authors_PK (author_pk char(11))
> OPEN keyword_cursor
> DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
> -- Fetch the first row in the cursor.
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.sql = 'insert into #authors_PK (author_pk) select au_id from
> authors where contains(*, ''' + @.keyword + ''')'
> exec(@.sql)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> select * from #authors_PK
> drop table #authors_PK
> go
> -- Clean-up
> drop table keyword
> go
> Let me know if this is what you're looking for.
> Regards,
> John
>
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:urOPK8jKEHA.3492@.TK2MSFTNGP09.phx.gbl...
> article
Queries"
> varchar(7800))
> Packard")
>

Column does not allow nulls

Hi,

This should be straight forward, but I've searched high and low on the net.

I have a FORM which allows me to INSERT data, SQL Server 2005 backend. I populate the all the mandatory fields.

But when I click on the Insert button, it won't let me save and says:

Column 'PERSONAL_ID' does not allow nulls

I'musing tableadapters, business logic layers etc. and pausing clearlyshows that the values from the form are being passed to the procedurethat I have created "AddNewRecord". And PERSONAL_ID is definitely notNULL!

It fails on

Line 971: Me.Rows.Add(row)

PERSONAL_IDis a primary key which I generate. The pause also shows that forPERSONAL_ID it says" {"Conversion from type 'DBNull' to type 'String'is not valid."}

Function AddNewRecord looks like this:

<System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public FunctionAddNewRecord(ByVal PERSONAL_ID As String, ByVal SURNAMEAs String, ByVal CHRISTIAN_NAME As String, ByVal SEX As String, _
ByVal FAMILY_POSITION As String, ByVal FAMILY_ID As String, ByValADDRESS_1 As String, ByVal ADDRESS_2 As String, _
ByVal ADDRESS_3As String, ByVal ADDRESS_4 As String, ByVal ADDRESS_5 As String, ByValADDRESS_6 As String, ByVal POSTCODE As String, ByVal COUNTRY As String,ByVal ORG_ID As String) As Boolean
' create a new details row instance
Dim details As New smDetails.smTbl_DetailsIDDataTable
Dim detail As smDetails.smTbl_DetailsIDRow = details.NewsmTbl_DetailsIDRow

details.AddsmTbl_DetailsIDRow(detail)
.
.
.
my formview code is this:

<InsertItemTemplate>
PERSONAL_ID:
<asp:TextBox ID="PERSONAL_IDTextBox" runat="server"
Text='<%# Bind("PERSONAL_ID") %>' AutoPostBack="True" />
<br />
SURNAME:
<asp:TextBox ID="SURNAMETextBox" runat="server" Text='<%# Bind("SURNAME") %>'
AutoPostBack="True" />
<br />
CHRISTIAN_NAME:
<asp:TextBox ID="CHRISTIAN_NAMETextBox" runat="server"
Text='<%# Bind("CHRISTIAN_NAME") %>' />
<br />
SEX:
<asp:TextBox ID="SEXTextBox" runat="server" Text='<%# Bind("SEX") %>' />
<br />
FAMILY_POSITION:
<asp:TextBox ID="FAMILY_POSITIONTextBox" runat="server"
Text='<%# Bind("FAMILY_POSITION") %>' />
<br />
FAMILY_ID:
<asp:TextBox ID="FAMILY_IDTextBox" runat="server"
Text='<%# Bind("FAMILY_ID") %>' />
<br />
ADDRESS_1:
<asp:TextBox ID="ADDRESS_1TextBox" runat="server"
Text='<%# Bind("ADDRESS_1") %>' />
<br />
ADDRESS_2:
<asp:TextBox ID="ADDRESS_2TextBox" runat="server"
Text='<%# Bind("ADDRESS_2") %>' />
<br />
ADDRESS_3:
<asp:TextBox ID="ADDRESS_3TextBox" runat="server"
Text='<%# Bind("ADDRESS_3") %>' />
<br />
ADDRESS_4:
<asp:TextBox ID="ADDRESS_4TextBox" runat="server"
Text='<%# Bind("ADDRESS_4") %>' />
<br />
ADDRESS_5:
<asp:TextBox ID="ADDRESS_5TextBox" runat="server"
Text='<%# Bind("ADDRESS_5") %>' />
<br />
ADDRESS_6:
<asp:TextBox ID="ADDRESS_6TextBox" runat="server"
Text='<%# Bind("ADDRESS_6") %>' />
<br />
POSTCODE:
<asp:TextBox ID="POSTCODETextBox" runat="server"
Text='<%# Bind("POSTCODE") %>' />
<br />
COUNTRY:
<asp:TextBox ID="COUNTRYTextBox" runat="server" Text='<%# Bind("COUNTRY") %>' />
<br />
ORG_ID:
<asp:TextBox ID="ORG_IDTextBox" runat="server" Text='<%# Bind("ORG_ID") %>' />
<br />
<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True"
CommandName="Insert" Text="Insert" />
<asp:LinkButton ID="InsertCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel" Text="Cancel" />
</InsertItemTemplate

Any pointers in the right direction would be appreciated.

Thanks in advance

Tushar

Can you post the stored procedure/sql statement that performs the insert?

|||

INSERT INTO smTbl_DetailsID (PERSONAL_ID, SURNAME, CHRISTIAN_NAME, SEX, FAMILY_POSITION, FAMILY_ID, ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, ADDRESS_5, ADDRESS_6, POSTCODE, COUNTRY, ORG_ID)VALUES (@.PERSONAL_ID,@.SURNAME,@.CHRISTIAN_NAME,@.SEX,@.FAMILY_POSITION,@.FAMILY_ID,@.ADDRESS_1,@.ADDRESS_2,@.ADDRESS_3,@.ADDRESS_4,@.ADDRESS_5,@.ADDRESS_6,@.POSTCODE,@.COUNTRY,@.ORG_ID)

Execute mode is Scalar

thanks

Tushar

|||

I've found the solution is to replace

details.AddsmTbl_DetailsIDRow(detail)

inFunction AddNewRecord in the BLL

with

Adapter1.InsertQuery_TAM(PERSONAL_ID, SURNAME, CHRISTIAN_NAME, SEX, _
FAMILY_POSITION, FAMILY_ID, ADDRESS_1, ADDRESS_2, _
ADDRESS_3, ADDRESS_4, ADDRESS_5, ADDRESS_6, POSTCODE, COUNTRY, ORG_ID)

i.e. my user defined insert query.

For some reason adding a new row directly causes the SELECT routine to run, which expects a value for the PERSONAL_ID (primary key). I'm sure I'll discover why in due course.

Also the above is not really very clear in the tutorials.

HTH

Tushar

Collation problem after migration from 2000 to 2005

Hi. Wondered if I might get a bit of advice on a minor collation problem I've experienced after migrating a database form SQL Server 2000 to 2005?

Users reported an error appearing in pages in a web-based application and I traced the problem to a stored procedure that was using a temporary table. I retained the original collation - SQL_Latin1_General_CP1_CI_AS - after migration and the error showing up was 'Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.' This makes sense as I guess temporary tables are assuming tempdb's collation. Resolution in this case was fairly simple, which was to apply an explicit collation to one of the columns in the temporary table (it only had three columns, only one requiring the collation). However the longer-term question is should I: (a) resolve similar issues in the same way as and when they arise; (b) change the collation of the migrated database to that of the default for system and new databases under 2005 - i.e. Latin1_General_CI_AS; or (b) change the collation of tempdb and the other system databases to match that of the migrated databases (sounds risky)?

I would probably go for (a) or perhaps (b) but could use some expert advise on the matter.

Regards,

YaHozna.I would try to avoid working with different collations on your server/db/objects unless absolutely necessary. One option is to script your database and all objects contained within it without specifying the collation, run the script on your 2005 server so your db and all objects pick up that servers default collation, then export the data from the 2000 db into the 2005 db. Once complete, you should be all sync'd up.|||Thanks DBriles10. Is this something I can do post-migration? If so what would be the desired order of events? I'm assuming something like this:

1. script the database in its new location under 2005 without collation
2. back up and detach the database
3. run the script to create a new database

I was going to say next, restore the original database under a different name in order to be able to import data from it to the new database however wouldn't that cause conflicts with object names? I guess restoring the original database to a different server to do the data import. Any thoughts?

Regards,

YaHozna.|||Your plan looks good to me. The restore of the database as a new name will not cause conflicts with object names.

If we call the existing DB with the bad collation "FixMe", you will...

1. Script "FixMe" and be sure to not include collation settings
2. Backup and detach "FixMe" >> "FixMe" no longer exists on server
3. Run the script from step 1 to recreate "FixMe" >> "FixMe" now exists with the server's default collation settings
4. Restore database from step 2 as "FixMe_Import" >> No object name conflicts as the objects reside in separate DB's and therefore are unaware of each other.
5. Import data from "FixMe_Import" to "FixMe"

For the import, I suggest setting all contraints to NoCheck, disabling all triggers and be aware of which tables have identities so you can turn identity_insert on.

Good luck.|||Many thanks for the input.

Regards,

YaHozna.

Collation problem (please help)

Hi,

when I install SQL Server it uses a SQL collation (SQL_Latin1_General_CP1_CI_AS), and i created a form haveing Arabic field names and every thing saved on my DataBase, when i call this field to the html page its look fine and visitor can read it , the problem when the clintes (visitors) fill this form and submit it, I can't read the information enterd in the text fields its shown like this (?).

can any one help

YOu will have to insert the values indicating that unicode is inserted. If you are composing your own insert statements, make sure you are using something like N'HereisUnicode' whereas the prefacing N indicated the Unicode character. Unicode data can be stored in unicode data types like Nvarchar etc. If you are no composing your statements on the fly and do parametrized queries (preferable) you should be aware of using the appropiate SQL unicode datatypes (mentioned above)

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

I'll try that,

thank you alot.