A simple Update query using a date - conversion from msaccess

I'm converting an ASP system from using msaccess to SQL Server as the db engine, and I'm stumped on the following query

update
timecard
set
TcdDate = #3/18/05#

TcdDate is defined as a date/time type

It will not run with the date bracketed by # signs, and when I take them out, 1/1/1900 is stored in the dbs. Is there a different symbol to bracket the date with or should I be using a function to convert the date?Converting a whole database into SQL Svr?? Why dont u try ur hand at writing a DTS package and running it. It does everything for u automatically though u need to spend some time writing it. It is absolutely reusable.|||Your sql stmt should be :

UPDATE
timecard
SET
TcdDate= '3/18/05'

In sql server the datetime datatype is treated as string hence you need to wrap it in quotes.|||Argh! I thought I had tried that already. But, in retrospect, I had coded TcdDate = 3/18/05, and thus my problem lay with a couple of single quotes.

Sometimes it takes a fresh set of eyes to look at a problem.

Thank you for your help.|||FWIW it would be safer to stick to ISO date format (YYYYMMDD). This will insure that the month and date portions are recognized correctly regardless of the language settings on the SQL Server:

UPDATE
 timecard
SET
 TcdDate = '20050318'

Terri

A query I''ve encounter that has me stumped ... anybody?

Hello Everyone,

I was hoping somebody could help me out with a query I've been trying to solve.

My Table Structure:

[UserMessages]

MessageID int
ToUserID int
FromUserID int
Subject varchar(200)
Message varchar(max)
isNew bit

[UserMessageReplies]

ReplyMessageID int
MessageID int
ToUserID int
FromUserID int
Message varchar(max)
isNew bit

Sample Data:

[UserMessages]

MessageID | ToUserID | FromUserID | Subject | Message | isNew
-
1 1 2 test subject Message Body 0
2 2 1 test subject Message Body 0
3 1 4 test subject Message Body 1
4 1 5 test subject Message Body 1

[UserMessageReplies]

ReplyMessageID | MessageID | ToUserID | FromUserID | Message | isNew

1 1 2 1 re: Message Body 0
2 1 1 2 re: Message Body 0
3 1 2 1 re: Message Body 0
4 1 1 2 re: Message Body 0
5 1 2 1 re: Message Body 1
6 2 1 2 re: Message Body 1

Explanation:

ReplyMessageID = 1-4 signfies that there is a thread response to MessageID 1 but they have been opened (isNew =0).
ReplyMessageID = 5 signfies that there is a new thread response to MessageID 1.
ReplyMessageID = 6 signifies that there is a reply from a message UserID=1 sent out (MessageID 2)

There needs to be a filter to check UserMessages.TOUserID=1 OR UserMessageReplies.ToUserID=1 to ensure that we can capture a msg that UserID=1 had sent out but now there is a reply to the Message by the User (in this example data i've setup MessageID = 2 to handle that possibility)

My Goal:

To select all messages for a ToUserID and order them by UserMessages.isNew or UserMessageReplies.isNew. This must include a message the user has sent but now has received a reply. (This means the first filter; UserMessages.ToUserID, must be overwritten with UserMessageReplies.ToUserID)

Thanks very much!

Chris

I can't quite follow what you want. Could you do a few additional things:

1. Turn your table structures into DDL and DML, so test SQL Statements can be written

2. Put out the results you want to see exactly. That would be a big help.

Thanks

|||CREATE TABLE [dbo].[UserMessages](
[MessageID] [int] IDENTITY(1,1) NOT NULL,
[ToUserID] [int] NOT NULL,
[FromUserID] [int] NOT NULL,
[Subject] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[isNew] [bit] NOT NULL CONSTRAINT [DF_UserMessages_isNew] DEFAULT ((1)),
[DateSent] [datetime] NOT NULL CONSTRAINT [DF_UserMessages_DateSent] DEFAULT (getdate()),
CONSTRAINT [PK_UserMessages] PRIMARY KEY CLUSTERED
(
[MessageID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[UserMessagesReplies](
[ReplyMessageID] [int] IDENTITY(1,1) NOT NULL,
[MessageID] [int] NOT NULL,
[ToUserID] [int] NOT NULL,
[FromUserID] [int] NOT NULL,
[Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[isNew] [bit] NOT NULL CONSTRAINT [DF_UserMessagesReplies_isNew] DEFAULT ((1)),
[DateSent] [datetime] NULL CONSTRAINT [DF_UserMessagesReplies_DateSent] DEFAULT (getdate()),
CONSTRAINT [PK_UserMessagesReplies] PRIMARY KEY CLUSTERED
(
[ReplyMessageID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Result Set:

MessageID | ToUserID | FromUserID
-
1 1 2
2 2 1
3 1 4
4 1 5

MessageID=2 should appear because there is a response (ReplyMessageID=6) ToUserID=1

Select From UserMessages Where ToUserID=1

Thank you!
|||

It looks like you need to union the first table with the second table:

create view messagesTogether

as

select messageId, toUserId, fromUserId

from userMessages

union

select replyMessageId, toUserId, fromUserId

from userMessages

then get it where the toUserId is the same

But MessageId and ReplyMessageId don't match. I am uncomfortable with these table structures, so this is probably my best current guess. Am I close, does this make sense?

A query I''ve encounter that has me stumped ... anybody?

Hello Everyone,

I was hoping somebody could help me out with a query I've been trying to solve.

My Table Structure:

[UserMessages]

MessageID int
ToUserID int
FromUserID int
Subject varchar(200)
Message varchar(max)
isNew bit

[UserMessageReplies]

ReplyMessageID int
MessageID int
ToUserID int
FromUserID int
Message varchar(max)
isNew bit

Sample Data:

[UserMessages]

MessageID | ToUserID | FromUserID | Subject | Message | isNew
-
1 1 2 test subject Message Body 0
2 2 1 test subject Message Body 0
3 1 4 test subject Message Body 1
4 1 5 test subject Message Body 1

[UserMessageReplies]

ReplyMessageID | MessageID | ToUserID | FromUserID | Message | isNew

1 1 2 1 re: Message Body 0
2 1 1 2 re: Message Body 0
3 1 2 1 re: Message Body 0
4 1 1 2 re: Message Body 0
5 1 2 1 re: Message Body 1
6 2 1 2 re: Message Body 1

Explanation:

ReplyMessageID = 1-4 signfies that there is a thread response to MessageID 1 but they have been opened (isNew =0).
ReplyMessageID = 5 signfies that there is a new thread response to MessageID 1.
ReplyMessageID = 6 signifies that there is a reply from a message UserID=1 sent out (MessageID 2)

There needs to be a filter to check UserMessages.TOUserID=1 OR UserMessageReplies.ToUserID=1 to ensure that we can capture a msg that UserID=1 had sent out but now there is a reply to the Message by the User (in this example data i've setup MessageID = 2 to handle that possibility)

My Goal:

To select all messages for a ToUserID and order them by UserMessages.isNew or UserMessageReplies.isNew. This must include a message the user has sent but now has received a reply. (This means the first filter; UserMessages.ToUserID, must be overwritten with UserMessageReplies.ToUserID)

Thanks very much!

Chris

I can't quite follow what you want. Could you do a few additional things:

1. Turn your table structures into DDL and DML, so test SQL Statements can be written

2. Put out the results you want to see exactly. That would be a big help.

Thanks

|||CREATE TABLE [dbo].[UserMessages](
[MessageID] [int] IDENTITY(1,1) NOT NULL,
[ToUserID] [int] NOT NULL,
[FromUserID] [int] NOT NULL,
[Subject] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[isNew] [bit] NOT NULL CONSTRAINT [DF_UserMessages_isNew] DEFAULT ((1)),
[DateSent] [datetime] NOT NULL CONSTRAINT [DF_UserMessages_DateSent] DEFAULT (getdate()),
CONSTRAINT [PK_UserMessages] PRIMARY KEY CLUSTERED
(
[MessageID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[UserMessagesReplies](
[ReplyMessageID] [int] IDENTITY(1,1) NOT NULL,
[MessageID] [int] NOT NULL,
[ToUserID] [int] NOT NULL,
[FromUserID] [int] NOT NULL,
[Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[isNew] [bit] NOT NULL CONSTRAINT [DF_UserMessagesReplies_isNew] DEFAULT ((1)),
[DateSent] [datetime] NULL CONSTRAINT [DF_UserMessagesReplies_DateSent] DEFAULT (getdate()),
CONSTRAINT [PK_UserMessagesReplies] PRIMARY KEY CLUSTERED
(
[ReplyMessageID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Result Set:

MessageID | ToUserID | FromUserID
-
1 1 2
2 2 1
3 1 4
4 1 5

MessageID=2 should appear because there is a response (ReplyMessageID=6) ToUserID=1

Select From UserMessages Where ToUserID=1

Thank you!
|||

It looks like you need to union the first table with the second table:

create view messagesTogether

as

select messageId, toUserId, fromUserId

from userMessages

union

select replyMessageId, toUserId, fromUserId

from userMessages

then get it where the toUserId is the same

But MessageId and ReplyMessageId don't match. I am uncomfortable with these table structures, so this is probably my best current guess. Am I close, does this make sense?

A query I''ve encounter that has me stumped ... anybody?

Hello Everyone,

I was hoping somebody could help me out with a query I've been trying to solve.

My Table Structure:

[UserMessages]

MessageID int
ToUserID int
FromUserID int
Subject varchar(200)
Message varchar(max)
isNew bit

[UserMessageReplies]

ReplyMessageID int
MessageID int
ToUserID int
FromUserID int
Message varchar(max)
isNew bit

Sample Data:

[UserMessages]

MessageID | ToUserID | FromUserID | Subject | Message | isNew
-
1 1 2 test subject Message Body 0
2 2 1 test subject Message Body 0
3 1 4 test subject Message Body 1
4 1 5 test subject Message Body 1

[UserMessageReplies]

ReplyMessageID | MessageID | ToUserID | FromUserID | Message | isNew

1 1 2 1 re: Message Body 0
2 1 1 2 re: Message Body 0
3 1 2 1 re: Message Body 0
4 1 1 2 re: Message Body 0
5 1 2 1 re: Message Body 1
6 2 1 2 re: Message Body 1

Explanation:

ReplyMessageID = 1-4 signfies that there is a thread response to MessageID 1 but they have been opened (isNew =0).
ReplyMessageID = 5 signfies that there is a new thread response to MessageID 1.
ReplyMessageID = 6 signifies that there is a reply from a message UserID=1 sent out (MessageID 2)

There needs to be a filter to check UserMessages.TOUserID=1 OR UserMessageReplies.ToUserID=1 to ensure that we can capture a msg that UserID=1 had sent out but now there is a reply to the Message by the User (in this example data i've setup MessageID = 2 to handle that possibility)

My Goal:

To select all messages for a ToUserID and order them by UserMessages.isNew or UserMessageReplies.isNew. This must include a message the user has sent but now has received a reply. (This means the first filter; UserMessages.ToUserID, must be overwritten with UserMessageReplies.ToUserID)

Thanks very much!

Chris

I can't quite follow what you want. Could you do a few additional things:

1. Turn your table structures into DDL and DML, so test SQL Statements can be written

2. Put out the results you want to see exactly. That would be a big help.

Thanks

|||CREATE TABLE [dbo].[UserMessages](
[MessageID] [int] IDENTITY(1,1) NOT NULL,
[ToUserID] [int] NOT NULL,
[FromUserID] [int] NOT NULL,
[Subject] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[isNew] [bit] NOT NULL CONSTRAINT [DF_UserMessages_isNew] DEFAULT ((1)),
[DateSent] [datetime] NOT NULL CONSTRAINT [DF_UserMessages_DateSent] DEFAULT (getdate()),
CONSTRAINT [PK_UserMessages] PRIMARY KEY CLUSTERED
(
[MessageID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[UserMessagesReplies](
[ReplyMessageID] [int] IDENTITY(1,1) NOT NULL,
[MessageID] [int] NOT NULL,
[ToUserID] [int] NOT NULL,
[FromUserID] [int] NOT NULL,
[Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[isNew] [bit] NOT NULL CONSTRAINT [DF_UserMessagesReplies_isNew] DEFAULT ((1)),
[DateSent] [datetime] NULL CONSTRAINT [DF_UserMessagesReplies_DateSent] DEFAULT (getdate()),
CONSTRAINT [PK_UserMessagesReplies] PRIMARY KEY CLUSTERED
(
[ReplyMessageID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Result Set:

MessageID | ToUserID | FromUserID
-
1 1 2
2 2 1
3 1 4
4 1 5

MessageID=2 should appear because there is a response (ReplyMessageID=6) ToUserID=1

Select From UserMessages Where ToUserID=1

Thank you!
|||

It looks like you need to union the first table with the second table:

create view messagesTogether

as

select messageId, toUserId, fromUserId

from userMessages

union

select replyMessageId, toUserId, fromUserId

from userMessages

then get it where the toUserId is the same

But MessageId and ReplyMessageId don't match. I am uncomfortable with these table structures, so this is probably my best current guess. Am I close, does this make sense?

A query I''ve encounter that has me stumped ... anybody?

Hello Everyone,

I was hoping somebody could help me out with a query I've been trying to solve.

My Table Structure:

[UserMessages]

MessageID int
ToUserID int
FromUserID int
Subject varchar(200)
Message varchar(max)
isNew bit

[UserMessageReplies]

ReplyMessageID int
MessageID int
ToUserID int
FromUserID int
Message varchar(max)
isNew bit

Sample Data:

[UserMessages]

MessageID | ToUserID | FromUserID | Subject | Message | isNew
-
1 1 2 test subject Message Body 0
2 2 1 test subject Message Body 0
3 1 4 test subject Message Body 1
4 1 5 test subject Message Body 1

[UserMessageReplies]

ReplyMessageID | MessageID | ToUserID | FromUserID | Message | isNew

1 1 2 1 re: Message Body 0
2 1 1 2 re: Message Body 0
3 1 2 1 re: Message Body 0
4 1 1 2 re: Message Body 0
5 1 2 1 re: Message Body 1
6 2 1 2 re: Message Body 1

Explanation:

ReplyMessageID = 1-4 signfies that there is a thread response to MessageID 1 but they have been opened (isNew =0).
ReplyMessageID = 5 signfies that there is a new thread response to MessageID 1.
ReplyMessageID = 6 signifies that there is a reply from a message UserID=1 sent out (MessageID 2)

There needs to be a filter to check UserMessages.TOUserID=1 OR UserMessageReplies.ToUserID=1 to ensure that we can capture a msg that UserID=1 had sent out but now there is a reply to the Message by the User (in this example data i've setup MessageID = 2 to handle that possibility)

My Goal:

To select all messages for a ToUserID and order them by UserMessages.isNew or UserMessageReplies.isNew. This must include a message the user has sent but now has received a reply. (This means the first filter; UserMessages.ToUserID, must be overwritten with UserMessageReplies.ToUserID)

Thanks very much!

Chris

I can't quite follow what you want. Could you do a few additional things:

1. Turn your table structures into DDL and DML, so test SQL Statements can be written

2. Put out the results you want to see exactly. That would be a big help.

Thanks

|||CREATE TABLE [dbo].[UserMessages](
[MessageID] [int] IDENTITY(1,1) NOT NULL,
[ToUserID] [int] NOT NULL,
[FromUserID] [int] NOT NULL,
[Subject] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[isNew] [bit] NOT NULL CONSTRAINT [DF_UserMessages_isNew] DEFAULT ((1)),
[DateSent] [datetime] NOT NULL CONSTRAINT [DF_UserMessages_DateSent] DEFAULT (getdate()),
CONSTRAINT [PK_UserMessages] PRIMARY KEY CLUSTERED
(
[MessageID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[UserMessagesReplies](
[ReplyMessageID] [int] IDENTITY(1,1) NOT NULL,
[MessageID] [int] NOT NULL,
[ToUserID] [int] NOT NULL,
[FromUserID] [int] NOT NULL,
[Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[isNew] [bit] NOT NULL CONSTRAINT [DF_UserMessagesReplies_isNew] DEFAULT ((1)),
[DateSent] [datetime] NULL CONSTRAINT [DF_UserMessagesReplies_DateSent] DEFAULT (getdate()),
CONSTRAINT [PK_UserMessagesReplies] PRIMARY KEY CLUSTERED
(
[ReplyMessageID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Result Set:

MessageID | ToUserID | FromUserID
-
1 1 2
2 2 1
3 1 4
4 1 5

MessageID=2 should appear because there is a response (ReplyMessageID=6) ToUserID=1

Select From UserMessages Where ToUserID=1

Thank you!
|||

It looks like you need to union the first table with the second table:

create view messagesTogether

as

select messageId, toUserId, fromUserId

from userMessages

union

select replyMessageId, toUserId, fromUserId

from userMessages

then get it where the toUserId is the same

But MessageId and ReplyMessageId don't match. I am uncomfortable with these table structures, so this is probably my best current guess. Am I close, does this make sense?