GETDATE() to insert into a datetime field

microsoft.public.sqlserver.programming

17 Dec 2005 7:22 PM

GTN170777

Hi there, may seem like a really silly question, but i am new to SQL.
I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
record behaviour to upload information to the SQL Server. one of the fields
is DateRegisterred which in Dreamweaver is a hiddenfield with the value set
to GETDATE() called datereg so on the upload behaviour Dreamweaver should
upload GETDATE() from datereg to thedatetimefield DateRegisterred on the SQL
server. However on trying this i get the following error message -

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
datetime from character string.
/driverjobs/candregistration.asp, line 161

Please any advise as how to fix this would be really really appreciated.

Many thanks

Gareth

17 Dec 2005 7:35 PM

John Bell

Hi

It looks like your field is being taken as a string and you have issues with
the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
instead of getdate() on it's own. If you want to truncate the time use
CONVERT(char(8),GETDATE(),112)

John

Show quote

"GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
>
> Please any advise as how to fix this would be really really appreciated.
>
> Many thanks
>
> Gareth

17 Dec 2005 10:10 PM

GTN170777

Thanks John, should i change it within the hidden field, or somewhere in the
code? i've tried changing it in the hidden field but still get the same error
message?

thanks for your help

Gareth

Show quote

"John Bell" wrote:

> Hi
>
> It looks like your field is being taken as a string and you have issues with
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
>
> John
>
> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@microsoft.com...
> > Hi there, may seem like a really silly question, but i am new to SQL.
> > I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> > record behaviour to upload information to the SQL Server. one of the
> > fields
> > is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> > set
> > to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> > upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> > SQL
> > server. However on trying this i get the following error message -
> >
> > Error Type:
> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> > datetime from character string.
> > /driverjobs/candregistration.asp, line 161
> >
> > Please any advise as how to fix this would be really really appreciated.
> >
> > Many thanks
> >
> > Gareth
>
>
>

17 Dec 2005 10:13 PM

GTN170777

Hi again,

I've included the line of code - Thanks again.

<td><input name="DateRegistered" type="hidden" id="DateRegistered"
value="CONVERT(char(8),GETDATE(),112)"></td>

Show quote

17 Dec 2005 10:36 PM

John Bell

Hi

You can't put SQL into your code like that, it should be in the query that
you call and then populate the field from this value (in the same way you
populate others!). If you can use the datetime that the insert is made,
then it can be missed out completely and the column defined as not nullable
with GETDATE() as the default.

John

Show quote

"GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
news:83A6E095-9200-4AA2-93F6-A40C78335320@microsoft.com...
> Hi again,
>
> I've included the line of code - Thanks again.
>
> <td><input name="DateRegistered" type="hidden" id="DateRegistered"
> value="CONVERT(char(8),GETDATE(),112)"></td>
>
> "John Bell" wrote:
>
>> Hi
>>
>> It looks like your field is being taken as a string and you have issues
>> with
>> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
>> instead of getdate() on it's own. If you want to truncate the time use
>> CONVERT(char(8),GETDATE(),112)
>>
>> John
>>
>> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
>> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@microsoft.com...
>> > Hi there, may seem like a really silly question, but i am new to SQL.
>> > I'm building as ASP application in Dreamweaver MX2004 i'm using the
>> > insert
>> > record behaviour to upload information to the SQL Server. one of the
>> > fields
>> > is DateRegisterred which in Dreamweaver is a hiddenfield with the value
>> > set
>> > to GETDATE() called datereg so on the upload behaviour Dreamweaver
>> > should
>> > upload GETDATE() from datereg to thedatetimefield DateRegisterred on
>> > the
>> > SQL
>> > server. However on trying this i get the following error message -
>> >
>> > Error Type:
>> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
>> > [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
>> > datetime from character string.
>> > /driverjobs/candregistration.asp, line 161
>> >
>> > Please any advise as how to fix this would be really really
>> > appreciated.
>> >
>> > Many thanks
>> >
>> > Gareth
>>
>>
>>

17 Dec 2005 11:21 PM

GTN170777

Ahh, I think i understand, the Date function GETDATE() needs to run
automatically on the SQL server not in the ASP code. Thank you for this, it
only leaves me with one problem.
On another page users are allowed to post information for a period of time
which they chose from a drop down list -

where the item label is forinstance 7 days & the value is
DATEADD(d,7,GETDATE())

They can chose from 7,14,21 or 28 days - their choice populates a field
called expirydate.

My theory apparently wont work now, any ideas as to how i can get around
this one?

Onced again thanks for your help

Gareth

Show quote

"John Bell" wrote:

> Hi
>
> You can't put SQL into your code like that, it should be in the query that
> you call and then populate the field from this value (in the same way you
> populate others!). If you can use the datetime that the insert is made,
> then it can be missed out completely and the column defined as not nullable
> with GETDATE() as the default.
>
> John
>
> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> news:83A6E095-9200-4AA2-93F6-A40C78335320@microsoft.com...
> > Hi again,
> >
> > I've included the line of code - Thanks again.
> >
> > <td><input name="DateRegistered" type="hidden" id="DateRegistered"
> > value="CONVERT(char(8),GETDATE(),112)"></td>
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> It looks like your field is being taken as a string and you have issues
> >> with
> >> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> >> instead of getdate() on it's own. If you want to truncate the time use
> >> CONVERT(char(8),GETDATE(),112)
> >>
> >> John
> >>
> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> >> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@microsoft.com...
> >> > Hi there, may seem like a really silly question, but i am new to SQL.
> >> > I'm building as ASP application in Dreamweaver MX2004 i'm using the
> >> > insert
> >> > record behaviour to upload information to the SQL Server. one of the
> >> > fields
> >> > is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> >> > set
> >> > to GETDATE() called datereg so on the upload behaviour Dreamweaver
> >> > should
> >> > upload GETDATE() from datereg to thedatetimefield DateRegisterred on
> >> > the
> >> > SQL
> >> > server. However on trying this i get the following error message -
> >> >
> >> > Error Type:
> >> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> >> > datetime from character string.
> >> > /driverjobs/candregistration.asp, line 161
> >> >
> >> > Please any advise as how to fix this would be really really
> >> > appreciated.
> >> >
> >> > Many thanks
> >> >
> >> > Gareth
> >>
> >>
> >>
>
>
>

18 Dec 2005 1:40 PM

John Bell

Hi

This may help with the previous issue http://www.aspfaq.com/show.asp?id=2347

Instead of giving a offset, you may want to use a date picker see
http://www.aspfaq.com/show.asp?id=2309, if you do go with the original
option then if you are building up a string for your update statement you
can do something like (untested!):

      <%
          SQL = "UPDATE table SET NextDate = DATEADD(dd," &
Request.Form("DateOffset") & ",GETDATE())"

          response.write sql
      %>

      If you are calling a stored procedure then you just need to pass the
offset value.

      John

Show quote

"GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
news:EC001645-9C1D-492D-B1A7-83E41668FC8C@microsoft.com...
> Ahh, I think i understand, the Date function GETDATE() needs to run
> automatically on the SQL server not in the ASP code. Thank you for this,
> it
> only leaves me with one problem.
> On another page users are allowed to post information for a period of time
> which they chose from a drop down list -
>
> where the item label is forinstance 7 days & the value is
> DATEADD(d,7,GETDATE())
>
> They can chose from 7,14,21 or 28 days - their choice populates a field
> called expirydate.
>
> My theory apparently wont work now, any ideas as to how i can get around
> this one?
>
> Onced again thanks for your help
>
> Gareth
>
> "John Bell" wrote:
>
>> Hi
>>
>> You can't put SQL into your code like that, it should be in the query
>> that
>> you call and then populate the field from this value (in the same way you
>> populate others!). If you can use the datetime that the insert is made,
>> then it can be missed out completely and the column defined as not
>> nullable
>> with GETDATE() as the default.
>>
>> John
>>
>> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
>> news:83A6E095-9200-4AA2-93F6-A40C78335320@microsoft.com...
>> > Hi again,
>> >
>> > I've included the line of code - Thanks again.
>> >
>> > <td><input name="DateRegistered" type="hidden" id="DateRegistered"
>> > value="CONVERT(char(8),GETDATE(),112)"></td>
>> >
>> > "John Bell" wrote:
>> >
>> >> Hi
>> >>
>> >> It looks like your field is being taken as a string and you have
>> >> issues
>> >> with
>> >> the format of the date. Try using using
>> >> CONVERT(char(19),GETDATE(),120)
>> >> instead of getdate() on it's own. If you want to truncate the time use
>> >> CONVERT(char(8),GETDATE(),112)
>> >>
>> >> John
>> >>
>> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
>> >> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@microsoft.com...
>> >> > Hi there, may seem like a really silly question, but i am new to
>> >> > SQL.
>> >> > I'm building as ASP application in Dreamweaver MX2004 i'm using the
>> >> > insert
>> >> > record behaviour to upload information to the SQL Server. one of the
>> >> > fields
>> >> > is DateRegisterred which in Dreamweaver is a hiddenfield with the
>> >> > value
>> >> > set
>> >> > to GETDATE() called datereg so on the upload behaviour Dreamweaver
>> >> > should
>> >> > upload GETDATE() from datereg to thedatetimefield DateRegisterred on
>> >> > the
>> >> > SQL
>> >> > server. However on trying this i get the following error message -
>> >> >
>> >> > Error Type:
>> >> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
>> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error
>> >> > converting
>> >> > datetime from character string.
>> >> > /driverjobs/candregistration.asp, line 161
>> >> >
>> >> > Please any advise as how to fix this would be really really
>> >> > appreciated.
>> >> >
>> >> > Many thanks
>> >> >
>> >> > Gareth
>> >>
>> >>
>> >>
>>
>>
>>

18 Dec 2005 3:25 PM

GTN170777

Thanks John, once again seems very useful, Just a quick question would
something like
CONVERT(char(8), DATEADD(dd," &
Request.Form("expirydate") & ",GETDATE(),112)

work? inserted into the default value?

Thanks

Show quote

"John Bell" wrote:

> Hi
>
> This may help with the previous issue http://www.aspfaq.com/show.asp?id=2347
>
> Instead of giving a offset, you may want to use a date picker see
> http://www.aspfaq.com/show.asp?id=2309, if you do go with the original
> option then if you are building up a string for your update statement you
> can do something like (untested!):
>
>       <%
>           SQL = "UPDATE table SET NextDate = DATEADD(dd," &
> Request.Form("DateOffset") & ",GETDATE())"
>
>           response.write sql
>       %>
>
>       If you are calling a stored procedure then you just need to pass the
> offset value.
>
>       John
>
>
> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> news:EC001645-9C1D-492D-B1A7-83E41668FC8C@microsoft.com...
> > Ahh, I think i understand, the Date function GETDATE() needs to run
> > automatically on the SQL server not in the ASP code. Thank you for this,
> > it
> > only leaves me with one problem.
> > On another page users are allowed to post information for a period of time
> > which they chose from a drop down list -
> >
> > where the item label is forinstance 7 days & the value is
> > DATEADD(d,7,GETDATE())
> >
> > They can chose from 7,14,21 or 28 days - their choice populates a field
> > called expirydate.
> >
> > My theory apparently wont work now, any ideas as to how i can get around
> > this one?
> >
> > Onced again thanks for your help
> >
> > Gareth
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> You can't put SQL into your code like that, it should be in the query
> >> that
> >> you call and then populate the field from this value (in the same way you
> >> populate others!). If you can use the datetime that the insert is made,
> >> then it can be missed out completely and the column defined as not
> >> nullable
> >> with GETDATE() as the default.
> >>
> >> John
> >>
> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> >> news:83A6E095-9200-4AA2-93F6-A40C78335320@microsoft.com...
> >> > Hi again,
> >> >
> >> > I've included the line of code - Thanks again.
> >> >
> >> > <td><input name="DateRegistered" type="hidden" id="DateRegistered"
> >> > value="CONVERT(char(8),GETDATE(),112)"></td>
> >> >
> >> > "John Bell" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> It looks like your field is being taken as a string and you have
> >> >> issues
> >> >> with
> >> >> the format of the date. Try using using
> >> >> CONVERT(char(19),GETDATE(),120)
> >> >> instead of getdate() on it's own. If you want to truncate the time use
> >> >> CONVERT(char(8),GETDATE(),112)
> >> >>
> >> >> John
> >> >>
> >> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> >> >> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@microsoft.com...
> >> >> > Hi there, may seem like a really silly question, but i am new to
> >> >> > SQL.
> >> >> > I'm building as ASP application in Dreamweaver MX2004 i'm using the
> >> >> > insert
> >> >> > record behaviour to upload information to the SQL Server. one of the
> >> >> > fields
> >> >> > is DateRegisterred which in Dreamweaver is a hiddenfield with the
> >> >> > value
> >> >> > set
> >> >> > to GETDATE() called datereg so on the upload behaviour Dreamweaver
> >> >> > should
> >> >> > upload GETDATE() from datereg to thedatetimefield DateRegisterred on
> >> >> > the
> >> >> > SQL
> >> >> > server. However on trying this i get the following error message -
> >> >> >
> >> >> > Error Type:
> >> >> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error
> >> >> > converting
> >> >> > datetime from character string.
> >> >> > /driverjobs/candregistration.asp, line 161
> >> >> >
> >> >> > Please any advise as how to fix this would be really really
> >> >> > appreciated.
> >> >> >
> >> >> > Many thanks
> >> >> >
> >> >> > Gareth
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

18 Dec 2005 7:01 PM

John Bell

Hi

If you mean the default for column, then you don't need dateadd e.g

CREATE TABLE MyDates ( id int, dateval datetime not null default
convert(char(8),getdate(),112))

INSERT INTO MyDates (id, dateval ) VALUES ( 1,GETDATE())

INSERT INTO MyDates (id) VALUES ( 2)

INSERT INTO MyDates (id, dateval) VALUES ( 3, '20051225' )

INSERT INTO MyDates (id, dateval) VALUES ( 4, '20051224 23:59:59' )

SELECT * FROM MyDates

John

Show quote

"GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
news:14E69D08-5318-4B52-9A1E-8DD3A7FB0F5E@microsoft.com...
> Thanks John, once again seems very useful, Just a quick question would
> something like
> CONVERT(char(8), DATEADD(dd," &
> Request.Form("expirydate") & ",GETDATE(),112)
>
> work? inserted into the default value?
>
> Thanks
>
> "John Bell" wrote:
>
>> Hi
>>
>> This may help with the previous issue
>> http://www.aspfaq.com/show.asp?id=2347
>>
>> Instead of giving a offset, you may want to use a date picker see
>> http://www.aspfaq.com/show.asp?id=2309, if you do go with the original
>> option then if you are building up a string for your update statement you
>> can do something like (untested!):
>>
>>       <%
>>           SQL = "UPDATE table SET NextDate = DATEADD(dd," &
>> Request.Form("DateOffset") & ",GETDATE())"
>>
>>           response.write sql
>>       %>
>>
>>       If you are calling a stored procedure then you just need to pass
>> the
>> offset value.
>>
>>       John
>>
>>
>> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
>> news:EC001645-9C1D-492D-B1A7-83E41668FC8C@microsoft.com...
>> > Ahh, I think i understand, the Date function GETDATE() needs to run
>> > automatically on the SQL server not in the ASP code. Thank you for
>> > this,
>> > it
>> > only leaves me with one problem.
>> > On another page users are allowed to post information for a period of
>> > time
>> > which they chose from a drop down list -
>> >
>> > where the item label is forinstance 7 days & the value is
>> > DATEADD(d,7,GETDATE())
>> >
>> > They can chose from 7,14,21 or 28 days - their choice populates a field
>> > called expirydate.
>> >
>> > My theory apparently wont work now, any ideas as to how i can get
>> > around
>> > this one?
>> >
>> > Onced again thanks for your help
>> >
>> > Gareth
>> >
>> > "John Bell" wrote:
>> >
>> >> Hi
>> >>
>> >> You can't put SQL into your code like that, it should be in the query
>> >> that
>> >> you call and then populate the field from this value (in the same way
>> >> you
>> >> populate others!). If you can use the datetime that the insert is
>> >> made,
>> >> then it can be missed out completely and the column defined as not
>> >> nullable
>> >> with GETDATE() as the default.
>> >>
>> >> John
>> >>
>> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
>> >> news:83A6E095-9200-4AA2-93F6-A40C78335320@microsoft.com...
>> >> > Hi again,
>> >> >
>> >> > I've included the line of code - Thanks again.
>> >> >
>> >> > <td><input name="DateRegistered" type="hidden" id="DateRegistered"
>> >> > value="CONVERT(char(8),GETDATE(),112)"></td>
>> >> >
>> >> > "John Bell" wrote:
>> >> >
>> >> >> Hi
>> >> >>
>> >> >> It looks like your field is being taken as a string and you have
>> >> >> issues
>> >> >> with
>> >> >> the format of the date. Try using using
>> >> >> CONVERT(char(19),GETDATE(),120)
>> >> >> instead of getdate() on it's own. If you want to truncate the time
>> >> >> use
>> >> >> CONVERT(char(8),GETDATE(),112)
>> >> >>
>> >> >> John
>> >> >>
>> >> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
>> >> >> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@microsoft.com...
>> >> >> > Hi there, may seem like a really silly question, but i am new to
>> >> >> > SQL.
>> >> >> > I'm building as ASP application in Dreamweaver MX2004 i'm using
>> >> >> > the
>> >> >> > insert
>> >> >> > record behaviour to upload information to the SQL Server. one of
>> >> >> > the
>> >> >> > fields
>> >> >> > is DateRegisterred which in Dreamweaver is a hiddenfield with the
>> >> >> > value
>> >> >> > set
>> >> >> > to GETDATE() called datereg so on the upload behaviour
>> >> >> > Dreamweaver
>> >> >> > should
>> >> >> > upload GETDATE() from datereg to thedatetimefield DateRegisterred
>> >> >> > on
>> >> >> > the
>> >> >> > SQL
>> >> >> > server. However on trying this i get the following error
>> >> >> > message -
>> >> >> >
>> >> >> > Error Type:
>> >> >> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
>> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error
>> >> >> > converting
>> >> >> > datetime from character string.
>> >> >> > /driverjobs/candregistration.asp, line 161
>> >> >> >
>> >> >> > Please any advise as how to fix this would be really really
>> >> >> > appreciated.
>> >> >> >
>> >> >> > Many thanks
>> >> >> >
>> >> >> > Gareth
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

18 Dec 2005 7:51 PM

GTN170777

Hi,

I think i understand -

Set the default field value to -
convert(char(8),getdate(),112))
Set the dropdown menu value to -
(id, dateval ) VALUES ( 1,GETDATE())

Would that do it - You've probably guessed that i am new to this, i really
do appreciate the assistance

thanks

Gareth

Show quote

"John Bell" wrote:

> Hi
>
> If you mean the default for column, then you don't need dateadd e.g
>
> CREATE TABLE MyDates ( id int, dateval datetime not null default
> convert(char(8),getdate(),112))
>
> INSERT INTO MyDates (id, dateval ) VALUES ( 1,GETDATE())
>
> INSERT INTO MyDates (id) VALUES ( 2)
>
> INSERT INTO MyDates (id, dateval) VALUES ( 3, '20051225' )
>
> INSERT INTO MyDates (id, dateval) VALUES ( 4, '20051224 23:59:59' )
>
> SELECT * FROM MyDates
>
> John
>
>
> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> news:14E69D08-5318-4B52-9A1E-8DD3A7FB0F5E@microsoft.com...
> > Thanks John, once again seems very useful, Just a quick question would
> > something like
> > CONVERT(char(8), DATEADD(dd," &
> > Request.Form("expirydate") & ",GETDATE(),112)
> >
> > work? inserted into the default value?
> >
> > Thanks
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> This may help with the previous issue
> >> http://www.aspfaq.com/show.asp?id=2347
> >>
> >> Instead of giving a offset, you may want to use a date picker see
> >> http://www.aspfaq.com/show.asp?id=2309, if you do go with the original
> >> option then if you are building up a string for your update statement you
> >> can do something like (untested!):
> >>
> >>       <%
> >>           SQL = "UPDATE table SET NextDate = DATEADD(dd," &
> >> Request.Form("DateOffset") & ",GETDATE())"
> >>
> >>           response.write sql
> >>       %>
> >>
> >>       If you are calling a stored procedure then you just need to pass
> >> the
> >> offset value.
> >>
> >>       John
> >>
> >>
> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> >> news:EC001645-9C1D-492D-B1A7-83E41668FC8C@microsoft.com...
> >> > Ahh, I think i understand, the Date function GETDATE() needs to run
> >> > automatically on the SQL server not in the ASP code. Thank you for
> >> > this,
> >> > it
> >> > only leaves me with one problem.
> >> > On another page users are allowed to post information for a period of
> >> > time
> >> > which they chose from a drop down list -
> >> >
> >> > where the item label is forinstance 7 days & the value is
> >> > DATEADD(d,7,GETDATE())
> >> >
> >> > They can chose from 7,14,21 or 28 days - their choice populates a field
> >> > called expirydate.
> >> >
> >> > My theory apparently wont work now, any ideas as to how i can get
> >> > around
> >> > this one?
> >> >
> >> > Onced again thanks for your help
> >> >
> >> > Gareth
> >> >
> >> > "John Bell" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> You can't put SQL into your code like that, it should be in the query
> >> >> that
> >> >> you call and then populate the field from this value (in the same way
> >> >> you
> >> >> populate others!). If you can use the datetime that the insert is
> >> >> made,
> >> >> then it can be missed out completely and the column defined as not
> >> >> nullable
> >> >> with GETDATE() as the default.
> >> >>
> >> >> John
> >> >>
> >> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> >> >> news:83A6E095-9200-4AA2-93F6-A40C78335320@microsoft.com...
> >> >> > Hi again,
> >> >> >
> >> >> > I've included the line of code - Thanks again.
> >> >> >
> >> >> > <td><input name="DateRegistered" type="hidden" id="DateRegistered"
> >> >> > value="CONVERT(char(8),GETDATE(),112)"></td>
> >> >> >
> >> >> > "John Bell" wrote:
> >> >> >
> >> >> >> Hi
> >> >> >>
> >> >> >> It looks like your field is being taken as a string and you have
> >> >> >> issues
> >> >> >> with
> >> >> >> the format of the date. Try using using
> >> >> >> CONVERT(char(19),GETDATE(),120)
> >> >> >> instead of getdate() on it's own. If you want to truncate the time
> >> >> >> use
> >> >> >> CONVERT(char(8),GETDATE(),112)
> >> >> >>
> >> >> >> John
> >> >> >>
> >> >> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> >> >> >> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@microsoft.com...
> >> >> >> > Hi there, may seem like a really silly question, but i am new to
> >> >> >> > SQL.
> >> >> >> > I'm building as ASP application in Dreamweaver MX2004 i'm using
> >> >> >> > the
> >> >> >> > insert
> >> >> >> > record behaviour to upload information to the SQL Server. one of
> >> >> >> > the
> >> >> >> > fields
> >> >> >> > is DateRegisterred which in Dreamweaver is a hiddenfield with the
> >> >> >> > value
> >> >> >> > set
> >> >> >> > to GETDATE() called datereg so on the upload behaviour
> >> >> >> > Dreamweaver
> >> >> >> > should
> >> >> >> > upload GETDATE() from datereg to thedatetimefield DateRegisterred
> >> >> >> > on
> >> >> >> > the
> >> >> >> > SQL
> >> >> >> > server. However on trying this i get the following error
> >> >> >> > message -
> >> >> >> >
> >> >> >> > Error Type:
> >> >> >> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error
> >> >> >> > converting
> >> >> >> > datetime from character string.
> >> >> >> > /driverjobs/candregistration.asp, line 161
> >> >> >> >
> >> >> >> > Please any advise as how to fix this would be really really
> >> >> >> > appreciated.
> >> >> >> >
> >> >> >> > Many thanks
> >> >> >> >
> >> >> >> > Gareth
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

18 Dec 2005 8:17 PM

John Bell

Hi

The drop down values need to be coded into your update statement such as

<%
SQL = "UPDATE table SET NextDate = DATEADD(dd," &
Request.Form("DateOffset") & ",GETDATE())"
%>

John

Show quote

"GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
news:4B0A8B65-1F20-4BC9-B62B-EDDDC6A33266@microsoft.com...
> Hi,
>
> I think i understand -
>
> Set the default field value to -
> convert(char(8),getdate(),112))
> Set the dropdown menu value to -
> (id, dateval ) VALUES ( 1,GETDATE())
>
> Would that do it - You've probably guessed that i am new to this, i really
> do appreciate the assistance
>
> thanks
>
> Gareth
>
> "John Bell" wrote:
>
>> Hi
>>
>> If you mean the default for column, then you don't need dateadd e.g
>>
>> CREATE TABLE MyDates ( id int, dateval datetime not null default
>> convert(char(8),getdate(),112))
>>
>> INSERT INTO MyDates (id, dateval ) VALUES ( 1,GETDATE())
>>
>> INSERT INTO MyDates (id) VALUES ( 2)
>>
>> INSERT INTO MyDates (id, dateval) VALUES ( 3, '20051225' )
>>
>> INSERT INTO MyDates (id, dateval) VALUES ( 4, '20051224 23:59:59' )
>>
>> SELECT * FROM MyDates
>>
>> John
>>
>>
>> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
>> news:14E69D08-5318-4B52-9A1E-8DD3A7FB0F5E@microsoft.com...
>> > Thanks John, once again seems very useful, Just a quick question would
>> > something like
>> > CONVERT(char(8), DATEADD(dd," &
>> > Request.Form("expirydate") & ",GETDATE(),112)
>> >
>> > work? inserted into the default value?
>> >
>> > Thanks
>> >
>> > "John Bell" wrote:
>> >
>> >> Hi
>> >>
>> >> This may help with the previous issue
>> >> http://www.aspfaq.com/show.asp?id=2347
>> >>
>> >> Instead of giving a offset, you may want to use a date picker see
>> >> http://www.aspfaq.com/show.asp?id=2309, if you do go with the original
>> >> option then if you are building up a string for your update statement
>> >> you
>> >> can do something like (untested!):
>> >>
>> >>       <%
>> >>           SQL = "UPDATE table SET NextDate = DATEADD(dd," &
>> >> Request.Form("DateOffset") & ",GETDATE())"
>> >>
>> >>           response.write sql
>> >>       %>
>> >>
>> >>       If you are calling a stored procedure then you just need to pass
>> >> the
>> >> offset value.
>> >>
>> >>       John
>> >>
>> >>
>> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
>> >> news:EC001645-9C1D-492D-B1A7-83E41668FC8C@microsoft.com...
>> >> > Ahh, I think i understand, the Date function GETDATE() needs to run
>> >> > automatically on the SQL server not in the ASP code. Thank you for
>> >> > this,
>> >> > it
>> >> > only leaves me with one problem.
>> >> > On another page users are allowed to post information for a period
>> >> > of
>> >> > time
>> >> > which they chose from a drop down list -
>> >> >
>> >> > where the item label is forinstance 7 days & the value is
>> >> > DATEADD(d,7,GETDATE())
>> >> >
>> >> > They can chose from 7,14,21 or 28 days - their choice populates a
>> >> > field
>> >> > called expirydate.
>> >> >
>> >> > My theory apparently wont work now, any ideas as to how i can get
>> >> > around
>> >> > this one?
>> >> >
>> >> > Onced again thanks for your help
>> >> >
>> >> > Gareth
>> >> >
>> >> > "John Bell" wrote:
>> >> >
>> >> >> Hi
>> >> >>
>> >> >> You can't put SQL into your code like that, it should be in the
>> >> >> query
>> >> >> that
>> >> >> you call and then populate the field from this value (in the same
>> >> >> way
>> >> >> you
>> >> >> populate others!). If you can use the datetime that the insert is
>> >> >> made,
>> >> >> then it can be missed out completely and the column defined as not
>> >> >> nullable
>> >> >> with GETDATE() as the default.
>> >> >>
>> >> >> John
>> >> >>
>> >> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
>> >> >> news:83A6E095-9200-4AA2-93F6-A40C78335320@microsoft.com...
>> >> >> > Hi again,
>> >> >> >
>> >> >> > I've included the line of code - Thanks again.
>> >> >> >
>> >> >> > <td><input name="DateRegistered" type="hidden"
>> >> >> > id="DateRegistered"
>> >> >> > value="CONVERT(char(8),GETDATE(),112)"></td>
>> >> >> >
>> >> >> > "John Bell" wrote:
>> >> >> >
>> >> >> >> Hi
>> >> >> >>
>> >> >> >> It looks like your field is being taken as a string and you have
>> >> >> >> issues
>> >> >> >> with
>> >> >> >> the format of the date. Try using using
>> >> >> >> CONVERT(char(19),GETDATE(),120)
>> >> >> >> instead of getdate() on it's own. If you want to truncate the
>> >> >> >> time
>> >> >> >> use
>> >> >> >> CONVERT(char(8),GETDATE(),112)
>> >> >> >>
>> >> >> >> John
>> >> >> >>
>> >> >> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in
>> >> >> >> message
>> >> >> >> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@microsoft.com...
>> >> >> >> > Hi there, may seem like a really silly question, but i am new
>> >> >> >> > to
>> >> >> >> > SQL.
>> >> >> >> > I'm building as ASP application in Dreamweaver MX2004 i'm
>> >> >> >> > using
>> >> >> >> > the
>> >> >> >> > insert
>> >> >> >> > record behaviour to upload information to the SQL Server. one
>> >> >> >> > of
>> >> >> >> > the
>> >> >> >> > fields
>> >> >> >> > is DateRegisterred which in Dreamweaver is a hiddenfield with
>> >> >> >> > the
>> >> >> >> > value
>> >> >> >> > set
>> >> >> >> > to GETDATE() called datereg so on the upload behaviour
>> >> >> >> > Dreamweaver
>> >> >> >> > should
>> >> >> >> > upload GETDATE() from datereg to thedatetimefield
>> >> >> >> > DateRegisterred
>> >> >> >> > on
>> >> >> >> > the
>> >> >> >> > SQL
>> >> >> >> > server. However on trying this i get the following error
>> >> >> >> > message -
>> >> >> >> >
>> >> >> >> > Error Type:
>> >> >> >> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
>> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error
>> >> >> >> > converting
>> >> >> >> > datetime from character string.
>> >> >> >> > /driverjobs/candregistration.asp, line 161
>> >> >> >> >
>> >> >> >> > Please any advise as how to fix this would be really really
>> >> >> >> > appreciated.
>> >> >> >> >
>> >> >> >> > Many thanks
>> >> >> >> >
>> >> >> >> > Gareth
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

18 Jan 2006 10:07 PM

GTN170777

Hi John,

Thanks for your help previously, i've now sorted it. However i have one last
problem -

Once the user has posted the advert it identifies all other users that match
the profile.

I'm trying to build a query from dreamweaver that identifies the advert
using a function like

Where adposted = Getdate()

However this doesn't work when the default value of the adposted column is
set to -

CONVERT(char(8),GETDATE(),112)

Any help would be appreciated

Show quote

"John Bell" wrote:

> Hi
>
> The drop down values need to be coded into your update statement such as
> ohn,

> <%
> SQL = "UPDATE table SET NextDate = DATEADD(dd," &
> Request.Form("DateOffset") & ",GETDATE())"
> %>
>
> John
>
> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> news:4B0A8B65-1F20-4BC9-B62B-EDDDC6A33266@microsoft.com...
> > Hi,
> >
> > I think i understand -
> >
> > Set the default field value to -
> > convert(char(8),getdate(),112))
> > Set the dropdown menu value to -
> > (id, dateval ) VALUES ( 1,GETDATE())
> >
> > Would that do it - You've probably guessed that i am new to this, i really
> > do appreciate the assistance
> >
> > thanks
> >
> > Gareth
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> If you mean the default for column, then you don't need dateadd e.g
> >>
> >> CREATE TABLE MyDates ( id int, dateval datetime not null default
> >> convert(char(8),getdate(),112))
> >>
> >> INSERT INTO MyDates (id, dateval ) VALUES ( 1,GETDATE())
> >>
> >> INSERT INTO MyDates (id) VALUES ( 2)
> >>
> >> INSERT INTO MyDates (id, dateval) VALUES ( 3, '20051225' )
> >>
> >> INSERT INTO MyDates (id, dateval) VALUES ( 4, '20051224 23:59:59' )
> >>
> >> SELECT * FROM MyDates
> >>
> >> John
> >>
> >>
> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> >> news:14E69D08-5318-4B52-9A1E-8DD3A7FB0F5E@microsoft.com...
> >> > Thanks John, once again seems very useful, Just a quick question would
> >> > something like
> >> > CONVERT(char(8), DATEADD(dd," &
> >> > Request.Form("expirydate") & ",GETDATE(),112)
> >> >
> >> > work? inserted into the default value?
> >> >
> >> > Thanks
> >> >
> >> > "John Bell" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> This may help with the previous issue
> >> >> http://www.aspfaq.com/show.asp?id=2347
> >> >>
> >> >> Instead of giving a offset, you may want to use a date picker see
> >> >> http://www.aspfaq.com/show.asp?id=2309, if you do go with the original
> >> >> option then if you are building up a string for your update statement
> >> >> you
> >> >> can do something like (untested!):
> >> >>
> >> >>       <%
> >> >>           SQL = "UPDATE table SET NextDate = DATEADD(dd," &
> >> >> Request.Form("DateOffset") & ",GETDATE())"
> >> >>
> >> >>           response.write sql
> >> >>       %>
> >> >>
> >> >>       If you are calling a stored procedure then you just need to pass
> >> >> the
> >> >> offset value.
> >> >>
> >> >>       John
> >> >>
> >> >>
> >> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> >> >> news:EC001645-9C1D-492D-B1A7-83E41668FC8C@microsoft.com...
> >> >> > Ahh, I think i understand, the Date function GETDATE() needs to run
> >> >> > automatically on the SQL server not in the ASP code. Thank you for
> >> >> > this,
> >> >> > it
> >> >> > only leaves me with one problem.
> >> >> > On another page users are allowed to post information for a period
> >> >> > of
> >> >> > time
> >> >> > which they chose from a drop down list -
> >> >> >
> >> >> > where the item label is forinstance 7 days & the value is
> >> >> > DATEADD(d,7,GETDATE())
> >> >> >
> >> >> > They can chose from 7,14,21 or 28 days - their choice populates a
> >> >> > field
> >> >> > called expirydate.
> >> >> >
> >> >> > My theory apparently wont work now, any ideas as to how i can get
> >> >> > around
> >> >> > this one?
> >> >> >
> >> >> > Onced again thanks for your help
> >> >> >
> >> >> > Gareth
> >> >> >
> >> >> > "John Bell" wrote:
> >> >> >
> >> >> >> Hi
> >> >> >>
> >> >> >> You can't put SQL into your code like that, it should be in the
> >> >> >> query
> >> >> >> that
> >> >> >> you call and then populate the field from this value (in the same
> >> >> >> way
> >> >> >> you
> >> >> >> populate others!). If you can use the datetime that the insert is
> >> >> >> made,
> >> >> >> then it can be missed out completely and the column defined as not
> >> >> >> nullable
> >> >> >> with GETDATE() as the default.
> >> >> >>
> >> >> >> John
> >> >> >>
> >> >> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in message
> >> >> >> news:83A6E095-9200-4AA2-93F6-A40C78335320@microsoft.com...
> >> >> >> > Hi again,
> >> >> >> >
> >> >> >> > I've included the line of code - Thanks again.
> >> >> >> >
> >> >> >> > <td><input name="DateRegistered" type="hidden"
> >> >> >> > id="DateRegistered"
> >> >> >> > value="CONVERT(char(8),GETDATE(),112)"></td>
> >> >> >> >
> >> >> >> > "John Bell" wrote:
> >> >> >> >
> >> >> >> >> Hi
> >> >> >> >>
> >> >> >> >> It looks like your field is being taken as a string and you have
> >> >> >> >> issues
> >> >> >> >> with
> >> >> >> >> the format of the date. Try using using
> >> >> >> >> CONVERT(char(19),GETDATE(),120)
> >> >> >> >> instead of getdate() on it's own. If you want to truncate the
> >> >> >> >> time
> >> >> >> >> use
> >> >> >> >> CONVERT(char(8),GETDATE(),112)
> >> >> >> >>
> >> >> >> >> John
> >> >> >> >>
> >> >> >> >> "GTN170777" <GTN170***@discussions.microsoft.com> wrote in
> >> >> >> >> message
> >> >> >> >> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@microsoft.com...
> >> >> >> >> > Hi there, may seem like a really silly question, but i am new
> >> >> >> >> > to
> >> >> >> >> > SQL.
> >> >> >> >> > I'm building as ASP application in Dreamweaver MX2004 i'm
> >> >> >> >> > using
> >> >> >> >> > the
> >> >> >> >> > insert
> >> >> >> >> > record behaviour to upload information to the SQL Server. one
> >> >> >> >> > of
> >> >> >> >> > the
> >> >> >> >> > fields
> >> >> >> >> > is DateRegisterred which in Dreamweaver is a hiddenfield with
> >> >> >> >> > the
> >> >> >> >> > value
> >> >> >> >> > set
> >> >> >> >> > to GETDATE() called datereg so on the upload behaviour
> >> >> >> >> > Dreamweaver
> >> >> >> >> > should
> >> >> >> >> > upload GETDATE() from datereg to thedatetimefield
> >> >> >> >> > DateRegisterred
> >> >> >> >> > on
> >> >> >> >> > the
> >> >> >> >> > SQL
> >> >> >> >> > server. However on trying this i get the following error
> >> >> >> >> > message -
> >> >> >> >> >
> >> >> >> >> > Error Type:
> >> >> >> >> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> >> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error
> >> >> >> >> > converting
> >> >> >> >> > datetime from character string.
> >> >> >> >> > /driverjobs/candregistration.asp, line 161
> >> >> >> >> >
> >> >> >> >> > Please any advise as how to fix this would be really really
> >> >> >> >> > appreciated.
> >> >> >> >> >
> >> >> >> >> > Many thanks
> >> >> >> >> >
> >> >> >> >> > Gareth
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

18 Jan 2006 11:31 PM

Erland Sommarskog

GTN170777 (GTN170***@discussions.microsoft.com) writes:

> Once the user has posted the advert it identifies all other users that
> match the profile.
>
> I'm trying to build a query from dreamweaver that identifies the advert
> using a function like
>
> Where adposted = Getdate()
>
> However this doesn't work when the default value of the adposted column is
> set to -
>
> CONVERT(char(8),GETDATE(),112)

I'm coming in late to this thread, but I think you should say:

WHERE adposted = CONVERT(char(8),GETDATE(),112)

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

18 Dec 2005 12:52 AM

Jon Paskett

Why not have the default value of the date field equal to getdate() in SQL
Server? If you're doing an INSERT it's a new record, set it then.

Jon

Show quote