Date insert issue into SQL server DateTime field?

[gus121]

Hi when i run this sql i get this error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.


The ASP

("INSERT INTO offer(OfferCode, OfferPercent, OfferStartDate, OfferFinishDate, OfferDescription) VALUES ('" & OfferCode & "'," & OfferPercent & ",'" & CDate(Now) & "','" & CDate(DateAdd("d", DaysActive, Now)) & "','" & offerDescription & "')")

The SQL:

INSERT INTO offer(OfferCode, OfferPercent, OfferStartDate, OfferFinishDate, OfferDescription) VALUES ('24082005-162928',1,'24/08/2005 16:29:28','04/08/2005 16:29:28','')

-Gus

 

[DotNetGnat]

sounds like a problem with the regional settings...check the datetime settings on your server...

also try this...

Format(yourdatefield, "dd-mm-yyyy hh:nn:ss") forcing to following the particular format...

-DNG

 

[FesterSXS]

perhaps using [red]mm[/red] instead of [red]nn[/red] though

Tony

Spirax-Sarco - steam traps control valves heat exchangers
Sun Villa - Luxury Florida accommodation

 

[FesterSXS]

ignore me - i'm being blond today - it is [red]nn[/red] for minutes isnt it

Tony

Spirax-Sarco - steam traps control valves heat exchangers
Sun Villa - Luxury Florida accommodation

 

[DotNetGnat]

yes.

-DNG

 

[gus121]

Hi DNG,

I know i probably been a bit dim but i tried this as i thought Format must be a sQL command:

INSERT INTO offer(OfferCode, OfferPercent, OfferStartDate, OfferFinishDate, OfferDescription) VALUES ('24082005-174507',1, Format(04/08/2005 17:45:07, dd-mm-yyyy hh:nn:ss), Format(24/08/2005 17:45:07, dd-mm-yyyy hh:nn:ss),'')
[\code]

I got various errors:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '17'.
Server: Msg 132, Level 15, State 1, Line 1
The label 'hh' has already been declared. Label names must be unique within a query batch or stored procedure.
Server: Msg 132, Level 15, State 1, Line 1
The label 'nn' has already been declared. Label names must be unique within a query batch or stored procedure.

-Gus

 

[damber]

it is an ASP function so resolve the value outside of your sql string

("INSERT INTO offer(OfferCode, OfferPercent, OfferStartDate, OfferFinishDate, OfferDescription) VALUES ('" & OfferCode & "'," & OfferPercent & ",'" & Format(Now, "dd-mm-yyyy hh:nn:ss")  & "','" & Format(DateAdd("d", DaysActive, Now), "dd-mm-yyyy hh:nn:ss") & "','" & offerDescription & "')")

Though I don't think this will work for you - I think you will need to change the month and the day bits around to be 'Americanised' - SQL server is probably reading the day (e.g. 24) as the month and telling you that there is no such month.. i.e. Out-of-range.

A smile is worth a thousand kind words. So smile, it's easy!

 

[gus121]

Hi damber,

I have never heared of an inbuilt asp format function!

response.write(Format(Now, "dd-mm-yyyy hh:nn:ss"))

When i run this i get back. Type mismatch: 'Format'

-Gus

 

[damber]

Oops, sorry - that's a VB function, not VBScript. You can build your own component (very simple) to run the format function in VB and pass it back to VBScript (ASP).

You should be able to change the LCID (Locale Setting) so that it uses the correct date time format for your SQL server.

here's a link:

http://www.webwizguide.info/asp/faq/date_time_settings.asp

or you could split the date into bits either as an array or using the Day() Month() year() functions and placing them in the order you prefer.



A smile is worth a thousand kind words. So smile, it's easy!

 

[damber]

Here's the code for full access to the VB Format Function:

http://www.4guysfromrolla.com/webtech/051601-1.shtml

A smile is worth a thousand kind words. So smile, it's easy!

 

[gus121]

Hi thanks damber,

I have looked at the information you have given me. I am aware how to create a vb function. thanks

Think its probably better to write a stored procedure and do the date manipulation bit within the proc therefore no need to change the Local Settings for the whole site!

thanks

-Gus

 

[damber]

Gus,

You should use Stored Procedures anyway - they are much more efficient, and helps to separate your application layer from the data layer.

You could just create a simple DateForSQL function that uses the DatePart or Day/Month/Year to present back a string that represents the correct format.

Or present the long version of the date (e.g. the full month name in particular) so there is no ambiguity, e.g. "August 25, 2005"

You can use the LCID inside a function and only apply it to the datetime you are working on at that time.

here's a link I found describing the issue quite well:

http://www.aspfaq.com/show.asp?id=2260

A smile is worth a thousand kind words. So smile, it's easy!

 

[DotNetGnat]

Gus,

post the current query that you are trying...and also post what errors are you getting...

-DNG

 

[DotNetGnat]

how about using some convert() functions...post back your query...

-DNG