Date/Time are giving me grief - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Date/Time are giving me grief

Author

Topic

whitesword
Starting Member

17 Posts

Posted - 2003-07-30 : 10:19:23

Hi,

I am an Access user, and have very recently been introduced into the wonderful world of SQL server. Boy, what a very different platform to work with. Anyway I am having teething problems with UPDATE and INSERT statements that were really easy in Access and are proving to be a BIG headache in SQL server.

Several rounds of aspirin, Books Online until my eyes hurt, and more aspirin later here I am.

Access Statement:
UPDATE dbUserInfo SET CurrentVisit=#" & Now() & "# WHERE UserID=" & iUserID

T-SQL Statement doesn't work like that obviously.
The other problem is national language. I am in New Zealand and like all good english speaking countries

we post our dates as dd/mm/yyyy, and not like the backward version of mm/dd/yyyy.

Do I have to rearrange my date and time portions to fit the "backward" version or is there a way I can use a CAST or CONVERT to do this? If there is a CAST or CONVERT, how do I use it in my SQL statement.

Oh yes, platform is ASP too.

Cheers
Roger

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-30 : 10:35:49

[CODE]
SET DATEFORMAT DMY

DECLARE @MYDATE DATETIME

SET @MYDATE = '23/08/1978'

SELECT @MYDATE

SELECT CONVERT(VARCHAR(10), @MYDATE, 103)

SELECT CONVERT(VARCHAR(10), @MYDATE, 101)

SELECT CONVERT(VARCHAR(10), @MYDATE, 111)
[/CODE]
The red number specifies the format of the date. Please search for the word CONVERT in BOL and you will get a complete list of the values possible to use. 103 however is the British/French standard.

Shadow to Light

whitesword
Starting Member

17 Posts

Posted - 2003-07-30 : 10:59:51

Look I am really new to this SQL server this, but an old hat with Access.
What's that got to do with my UPDATE statement?

For example, in my ASP page it's something like:

mydate = Request.Form("dateField")

"mydate" comes in format when displayed "30/07/2003 3:03:15 am."

In Access the statement would be

"UPDATE dbUserInfo SET CurrentVisit=#" & mydate & "# WHERE UserID=" & iUserID

What's the equivalent in SQL server?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-07-30 : 11:05:14

Preceeding YOUR UPDATE STATMENT with the SET DATEFORMAT DMY statement will get the date values interpreted properly by SQL.

Alternatively, if ALL DATES are input/referenced as yyyymmdd, then SQL will work properly with no need for a DATEFORMAT command.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-07-30 : 11:05:53

1. WRITE A Stored Procedure with input parameters.
2. USE ADO Command Object to call stored procedure and pass parameters

SP should have an input parameter of @UserID.

UPDATE dbUserInfo
SET CurrentVisit = CONVERT(VARCHAR(10), GETDATE(), 103)
WHERE UserID = @UserID

whitesword
Starting Member

17 Posts

Posted - 2003-07-30 : 11:20:33

Thank you very much ValterBorges.

It was the methodology of the process that was giving me a headache. Calling the ADO command is normalll how I do things, but with your post a big bell went off in my head and all sorts of lights of inspiration flashed through my mind.

Those two lines were just what I needed.

Again, thanks for turning on the lights

ann_sqlteam
Starting Member

8 Posts

Posted - 2003-07-30 : 11:38:26

I too moved to sql server after doing a lot of Access development.

You don't _have_ to create a stored procedure.

You can use your UPDATE statement as is - just replace the # by apostrophes ('). SQL Server does not recognize # as a date delimiter.

Further, if connecting to Access using ODBC, you can use ' in place of # when issuing UPDATE statements.

Hope this helps.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-30 : 11:44:42

quote:
Originally posted by ann_sqlteam
You don't _have_ to create a stored procedure.

True

quote:

You can use your UPDATE statement as is - just replace the # by apostrophes ('). SQL Server does not recognize # as a date delimiter.

And then not take any advantage of the optimizer for performance gains..

The quicker you strat writing stored procedures, the better off you'll be

My Own Opinion (MOO)

EDIT: And do you know what Books Online is? (BOL) Open it up and leave it open...great resource

Brett

8-)

whitesword
Starting Member

17 Posts

Posted - 2003-07-30 : 11:56:49

quote:

You can use your UPDATE statement as is - just replace the # by apostrophes ('). SQL Server does not recognize # as a date delimiter.

No I couldn't. Because of our date format, it kept coming up with conversion error datetime to varchar. That's what started me on this path of headaches. I even tried converting it to a double, but it kept adding two days to it (sometimes). Wasn't quite what I had in mind.

quote:

The quicker you strat writing stored procedures, the better off you'll be

Now that I know what's going on, I'll agree with that

quote:

And do you know what Books Online is? (BOL) Open it up and leave it open...great resource

It is now that I know what I'm looking for

The Northwind sample is also a good place to get ideas on how to implement some of the processes too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources