Date problems in Access and SQL server 7.0
| Author |
Message |
|
Lars Olse
#1 / 14
|
 Date problems in Access and SQL server 7.0 I have a VB6 program, and in that program I have an SQL statement, that I'm trying to run on both Access database and SQL server 7.0. The databases (Access *.mdb and SQL Server db) has the same structure. In the database I have a Date field.
In my SQL statement I need to update the date field with the current date.
When I use the Access database, I can use something like this
ATimeStamp='" & CDate(Now())"
But... this refuses to work on my SQL server...
Any suggestions?
(My regional settings are Danish, if it can help anyone)
Best Regards, Lars Olsen :o)
|
| Fri, 14 Nov 2003 17:21:23 GMT |
|
 |
|
Dave
#2 / 14
|
Date problems in Access and SQL server 7.0 I don't have SQL Server but can you use the SQL GETDATE() function? Quote:
>I have a VB6 program, and in that program I have an SQL statement, that I'm
>trying to run on both Access database and SQL server 7.0. The databases
>(Access *.mdb and SQL Server db) has the same structure.
|
| Fri, 14 Nov 2003 17:56:37 GMT |
|
|
|
Gordon Youn
#3 / 14
|
Date problems in Access and SQL server 7.0 Try: "ATimeStamp = '" & Format(Now(),"short date") & "'" (I assume the above is part of creating a SQL statement in VB Code, eg:
strSQL = "UPDATE table SET ATimeStamp='" ...
or similar.
Not sure if this will work in Access, you might need something like:
"ATimeStamp=#" & format(Now(),"short date") & "#"
Thus you may need an IF statement or something...
Quote: -----Original Message----- I have a VB6 program, and in that program I have an SQL statement, that I'm
trying to run on both Access database and SQL server 7.0. The databases
(Access *.mdb and SQL Server db) has the same structure.
In the database I have a Date field.
In my SQL statement I need to update the date field with the current date.
When I use the Access database, I can use something like this
ATimeStamp='" & CDate(Now())"
But... this refuses to work on my SQL server...
Any suggestions?
(My regional settings are Danish, if it can help anyone)
Best Regards, Lars Olsen :o)
.
|
| Sun, 16 Nov 2003 05:04:10 GMT |
|
|
|
Max
#4 / 14
|
Date problems in Access and SQL server 7.0 Why are you implementing the Now() - function and the CDate - functions into a string? I would build the querystring something like tis:
qStr = "UPDATE <tableName> SET <fieldName>='" & CDate(Now()) "' WHERE
<Index>=" & <IndexNo>
The brackets indicate that you should fill in your own
table/variable/field - names...
The Now() - function should return a variant variable of sub-type Date, so
it should be no need for the CDate - function. A variant of subtype Date and
a Date should look the same when they are converted to strings..
As I haven't worked a lot with SQL server, I'll also suggest that you may
consider to check out if there are some other date-settings in the SQL
server that ovverride the regional settings on the server computer.
I have some bad experiences using dates myself, as I often use norwegian
settings. But as long as the server and the client are on the same computer
it usually works well. If not, you'll have to check out the server settings,
and convert the date-strings accordingly.
Best regards, Max Wittussen
Quote: > I have a VB6 program, and in that program I have an SQL statement, that I'm > trying to run on both Access database and SQL server 7.0. The databases > (Access *.mdb and SQL Server db) has the same structure. > In the database I have a Date field.
> In my SQL statement I need to update the date field with the current date.
> When I use the Access database, I can use something like this
> ATimeStamp='" & CDate(Now())"
> But... this refuses to work on my SQL server...
> Any suggestions?
> (My regional settings are Danish, if it can help anyone)
> Best Regards, Lars Olsen :o)
|
| Sat, 15 Nov 2003 18:40:35 GMT |
|
|
|
Numbe
#5 / 14
|
Date problems in Access and SQL server 7.0 On Sat, 21 Jul 2001 23:49:27 GMT, "Lars Olsen" Quote:
>I have a VB6 program, and in that program I have an SQL statement, that I'm >trying to run on both Access database and SQL server 7.0. The databases >(Access *.mdb and SQL Server db) has the same structure. >In the database I have a Date field.
>In my SQL statement I need to update the date field with the current date.
>When I use the Access database, I can use something like this
>ATimeStamp='" & CDate(Now())"
>But... this refuses to work on my SQL server...
>Any suggestions?
>(My regional settings are Danish, if it can help anyone)
>Best Regards, Lars Olsen :o)
I found out I could not put a date variable in an SQL string, it had to be changed to a string first then concatenated into the SQL string. Hope that helps...
=====================================================
Did you know that cats can type sitting *on* the
keyboard?
http://www3.sympatico.ca/richmann/
http://www.geocities.com/richmannsoft/
=====================================================
|
| Thu, 08 Jan 2004 22:10:38 GMT |
|
|
|
Pabl
#6 / 14
|
Date problems in Access and SQL server 7.0 Try this and be happy; Sqlstatement = "Update [MY.TABLE] set [MY.DATEFIELD] = CURRENT DATE"
Grtz
Pablo
Quote: > On Sat, 21 Jul 2001 23:49:27 GMT, "Lars Olsen"
> >I have a VB6 program, and in that program I have an SQL statement, that
I'm
> >trying to run on both Access database and SQL server 7.0. The databases
> >(Access *.mdb and SQL Server db) has the same structure.
> >In the database I have a Date field.
> >In my SQL statement I need to update the date field with the current
date.
> >When I use the Access database, I can use something like this
> >ATimeStamp='" & CDate(Now())"
> >But... this refuses to work on my SQL server...
> >Any suggestions?
> >(My regional settings are Danish, if it can help anyone)
> >Best Regards, Lars Olsen :o)
> I found out I could not put a date variable in an SQL string, it had
> to be changed to a string first then concatenated into the SQL string.
> Hope that helps...
> =====================================================
> Did you know that cats can type sitting *on* the
> keyboard?
> http://www3.sympatico.ca/richmann/
> http://www.geocities.com/richmannsoft/
> =====================================================
|
| Tue, 13 Jan 2004 05:24:39 GMT |
|
|
|
Luís Pequeno da Cru
#7 / 14
|
Date problems in Access and SQL server 7.0 Never do this CDate(Now()) for Sql Query Strings. Remeber that date in a SQL statment are between #, and starting with month, plus day and year (ANSI SQL IBM85) Example: Select * From Recordset Where [Date] Between #mm-dd-yyyy# And #mm-dd-yyyy# For changing the date do this
Dim strInitialDate As String
Dim strFinalDate As String
strInitialDate = Cstr(Format (Date, "mm-dd-yyyy"))
strFinalDate = Cstr(Format (Date, "mm-dd-yyyy"))
Data1.Recordsource = "Select * From [Recordset] Where [Date] Between #"
& strInitialDate & "# And #" & str FinalDate &"#"
Data1.Refresh
If you've any doubt please contact me
--
Lus Pequeno da Cruz
Quote: > I have a VB6 program, and in that program I have an SQL statement, that I'm > trying to run on both Access database and SQL server 7.0. The databases > (Access *.mdb and SQL Server db) has the same structure. > In the database I have a Date field.
> In my SQL statement I need to update the date field with the current date.
> When I use the Access database, I can use something like this
> ATimeStamp='" & CDate(Now())"
> But... this refuses to work on my SQL server...
> Any suggestions?
> (My regional settings are Danish, if it can help anyone)
> Best Regards, Lars Olsen :o)
|
| Thu, 15 Jan 2004 04:10:52 GMT |
|
|
|
Hercules Gunte
#8 / 14
|
Date problems in Access and SQL server 7.0
Quote: > Never do this CDate(Now()) for Sql Query Strings.
> Remeber that date in a SQL statment are between #, and starting with
> month, plus day and year (ANSI SQL IBM85)
Actually, dates follow the Windows date specification (on Windows systems, obviously), and that, for most of the world, is emphatically not month/day/year. For most of us, it's day/month/year, and for some it's year/month/day. The safest approach is to use month names instead of month numbers - but note that the Windows date-handling routines are very smart, and will assume that if one of the dat/month components is > 12 and the other not that the big one is a month; if both are under 13, the routines assume the Windows date format. In other words: 1/2/1 is 1st Feb 2001 for us sane types, 2nd Jan 2001 for you freaky Americans
(crouches behind asbestos fence, muffled voice continues)
13/1/01 is 13th Jan 2001 regardless
1/23/1901 is the 23rd Jan 1901 regardless
(I've not played with y/m/d format, so I'm making no pronouncements.)
|
| Fri, 16 Jan 2004 22:11:28 GMT |
|
|
|
Darryl Sever
#9 / 14
|
Date problems in Access and SQL server 7.0 Actually, your comments make logical sense but don't necessarily work in practice. I too come from the "sane" part of the world re date formats, however that has confused the issue even more. I well remember one experience with SQL server that had me completely baffled for quite some time. I was trying to get a data set that met certain date criteria, and it was failing miserably. My initial reaction was the date format supplied didn't match the expected format. So I cut the code out of VB (running in debug mode allowed me to get at the exact string being sent to SQL Server) and pasted it into an SQL query box direct in SQL Server Enterprise Manager - AND IT WORKED!!! I then made the incorrect assumption that something else was wrong. Eventually I found out that programmatic code required the mdy format, but when entering directly in the database it used the local machine date format!!! Beat that one!!!
Darryl
Quote:
> > Never do this CDate(Now()) for Sql Query Strings.
> > Remeber that date in a SQL statment are between #, and starting with
> > month, plus day and year (ANSI SQL IBM85)
> Actually, dates follow the Windows date specification (on Windows systems,
> obviously), and that, for most of the world, is emphatically not
> month/day/year. For most of us, it's day/month/year, and for some it's
> year/month/day. The safest approach is to use month names instead of
month
> numbers - but note that the Windows date-handling routines are very smart,
and
> will assume that if one of the dat/month components is > 12 and the other
not
> that the big one is a month; if both are under 13, the routines assume the
> Windows date format. In other words:
> 1/2/1 is 1st Feb 2001 for us sane types, 2nd Jan 2001 for you freaky
Americans
> (crouches behind asbestos fence, muffled voice continues)
> 13/1/01 is 13th Jan 2001 regardless
> 1/23/1901 is the 23rd Jan 1901 regardless
> (I've not played with y/m/d format, so I'm making no pronouncements.)
|
| Sat, 17 Jan 2004 19:38:12 GMT |
|
|
|
David Wimbu
#10 / 14
|
Date problems in Access and SQL server 7.0 SQL Server has a setting that tells it how to interpret dates. It will either interpret '01/02/2001' as 1st February or as 2nd January. The setting could be changed at any time. If you're building a date into a SQL string the best thing to do is to convert it into a string yourself and make the format unambiguous. Use "'" & Format$(dteDOB,"dd-mmm-yyyy") & "'" for SQL Server or Sybase. Replace "'" with "#" for Access. Now the only thing you have to worry about is the time part of the
date screwing comparisons up: 01-FEB-2001 <> 01-FEB-2001 13:45:27!
|
| Sun, 18 Jan 2004 16:35:31 GMT |
|
|
|
Joe
#11 / 14
|
Date problems in Access and SQL server 7.0 Quote:
> SQL Server has a setting that tells it how to interpret dates. It will > either interpret '01/02/2001' as 1st February or as 2nd January. The > setting could be changed at any time. If you're building a date into a > SQL string the best thing to do is to convert it into a string > yourself and make the format unambiguous. > Use "'" & Format$(dteDOB,"dd-mmm-yyyy") & "'" for SQL Server or > Sybase. Replace "'" with "#" for Access. > Now the only thing you have to worry about is the time part of the
> date screwing comparisons up: 01-FEB-2001 <> 01-FEB-2001 13:45:27!
Does SQL Server understand the ISO yyyy-mm-dd date format? That would avoid the regionalization/language issues you could get with mmm. You could also compare them as good old strings, even if they had a time- of-day component. --
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
|
| Tue, 20 Jan 2004 11:13:28 GMT |
|
|
|
mike
#12 / 14
|
Date problems in Access and SQL server 7.0 since your using this as CDate.. then youre using a variant type. You can convert it to a string and call it back as a CString instead. besides there are a lot of date variable matters that should be worked out in
VB.
I just hassled for thee days with calculations of them.
Quote:
> I have a VB6 program, and in that program I have an SQL statement, that I'm > trying to run on both Access database and SQL server 7.0. The databases > (Access *.mdb and SQL Server db) has the same structure. > In the database I have a Date field.
> In my SQL statement I need to update the date field with the current date.
> When I use the Access database, I can use something like this
> ATimeStamp='" & CDate(Now())"
> But... this refuses to work on my SQL server...
> Any suggestions?
> (My regional settings are Danish, if it can help anyone)
> Best Regards, Lars Olsen :o)
-- From Mike! --------------------------- If So = True Then Debug.Print "Age Apertum!" End if ===========================
|
| Fri, 05 Mar 2004 04:43:36 GMT |
|
|
|
Graha
#13 / 14
|
Date problems in Access and SQL server 7.0 In all database work, you should use the International Date format of "YYYY/MM/DD" or "DD-MMM-YYYY", surrounded by it's appropriate date qualifier for the database (#, ", ', {) for Access, SQL Server, Sybase, FoxPro. Regards, Graham.
Quote: > Actually, your comments make logical sense but don't necessarily work in > practice. I too come from the "sane" part of the world re date formats, > however that has confused the issue even more. I well remember one > experience with SQL server that had me completely baffled for quite some > time. I was trying to get a data set that met certain date criteria, and it > was failing miserably. My initial reaction was the date format supplied > didn't match the expected format. So I cut the code out of VB (running in > debug mode allowed me to get at the exact string being sent to SQL Server) > and pasted it into an SQL query box direct in SQL Server Enterprise > Manager - AND IT WORKED!!! I then made the incorrect assumption that > something else was wrong. Eventually I found out that programmatic code > required the mdy format, but when entering directly in the database it used > the local machine date format!!! > Beat that one!!!
> Darryl
> > > Never do this CDate(Now()) for Sql Query Strings.
> > > Remeber that date in a SQL statment are between #, and starting
with
> > > month, plus day and year (ANSI SQL IBM85)
> > Actually, dates follow the Windows date specification (on Windows
systems,
> > obviously), and that, for most of the world, is emphatically not
> > month/day/year. For most of us, it's day/month/year, and for some it's
> > year/month/day. The safest approach is to use month names instead of
> month
> > numbers - but note that the Windows date-handling routines are very
smart,
> and
> > will assume that if one of the dat/month components is > 12 and the
other
> not
> > that the big one is a month; if both are under 13, the routines assume
the
> > Windows date format. In other words:
> > 1/2/1 is 1st Feb 2001 for us sane types, 2nd Jan 2001 for you freaky
> Americans
> > (crouches behind asbestos fence, muffled voice continues)
> > 13/1/01 is 13th Jan 2001 regardless
> > 1/23/1901 is the 23rd Jan 1901 regardless
> > (I've not played with y/m/d format, so I'm making no pronouncements.)
|
| Fri, 05 Mar 2004 14:09:03 GMT |
|
|
|
Alessio Deian
#14 / 14
|
Date problems in Access and SQL server 7.0 You can use the format #mm/dd/yyyy# to. You can send it coding: Dim strDateSQL as String strDateSQL = "#" & format(Now, "mm/dd/yyyy") & "#"
Quote: > since your using this as CDate.. then youre using a variant type. > You can convert it to a string and call it back as a CString instead. > besides there are a lot of date variable matters that should be worked out
in
> VB.
> I just hassled for thee days with calculations of them.
> > I have a VB6 program, and in that program I have an SQL statement, that
I'm
> > trying to run on both Access database and SQL server 7.0. The databases
> > (Access *.mdb and SQL Server db) has the same structure.
> > In the database I have a Date field.
> > In my SQL statement I need to update the date field with the current
date.
> > When I use the Access database, I can use something like this
> > ATimeStamp='" & CDate(Now())"
> > But... this refuses to work on my SQL server...
> > Any suggestions?
> > (My regional settings are Danish, if it can help anyone)
> > Best Regards, Lars Olsen :o)
> --
> From Mike!
> ---------------------------
> If So = True Then
> Debug.Print "Age Apertum!"
> End if
> ===========================
|
| Fri, 05 Mar 2004 15:02:20 GMT |
|
| |
|
