ASP calendar - date conversion error

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

General SQL Server Forums

New to SQL Server Programming

ASP calendar - date conversion error

Author

Topic

SHARDIK-GUARDIAN
Starting Member

20 Posts

Posted - 2009-04-07 : 05:20:53

Hi everyone!

This is my first post. So I hope I do not break any rules and this is the right place to put it.

I have a form which uses the free ASP Calendar (c) 2000, Elian Chrebor, myLittleTools.net

The calendar is currently configured for US date format (I'm in Europe). So I changed the code so it shows the UK/Euro date format.

Old code:

    myJSstr = myJSstr & vbTab & "if (pClose) setInfo(pMonth+'/'+pDay+'/'+pYear);" & vbCrlf

New code:

    myJSstr = myJSstr & vbTab & "if (pClose) setInfo(pDay+'/'+pMonth+'/'+pYear);" & vbCrlf

It was working fine until I tested the form with an 2nd date (this form can make requests with only a 1st date, or a 1st and 2nd date).

The form has no problem writing one of the dates to the DB, but the second date gives this error on the page:

quote:
Err = The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I;ve tried changing the field type in the DB from datetime to varchar, but no luck.

I think this is the code which is causing the issue:



strSql     = "INSERT INTO tbl_Requests (RequestDate, " & _
							 "GameType, FirstDate, SecondDate, Comments)" & _
        	                 "VALUES (CONVERT(datetime,'" & date & "') , '" SelectedGame & _
            	             "','" & GameType & "', CONVERT(datetime,'" & FirstDate & "'),"  & _
                	         "CONVERT(datetime,'" & SecondDate & "'),'" & Comment & "')"

(I had to edit the code a bit to publish it.)

I hope someone can help. I'm a real n00b with this so please have patience with me :)

Thanks all!

Was there ever a trap to match the trap of love?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 05:23:15

You should use ISO dateformat style when working with databases.

myJSstr = myJSstr & vbTab & "if (pClose) setInfo(pYear + '-' + pMonth + '-' + pDay);" & vbCrlf

E 12°55'05.63"
N 56°04'39.26"

SHARDIK-GUARDIAN
Starting Member

20 Posts

Posted - 2009-04-07 : 05:28:45

quote:
Originally posted by Peso

You should use ISO dateformat style when working with databases.

myJSstr = myJSstr & vbTab & "if (pClose) setInfo(pYear + '-' + pMonth + '-' + pDay);" & vbCrlf

E 12°55'05.63"
N 56°04'39.26"

Thanks for the super quick reply! :)

I tried it and it gives me this error:

quote:
Err = Error converting data type varchar to float.

Should I also change the DB field from "datetime" to "varchar"?

1,000 thanks!

Was there ever a trap to match the trap of love?

SHARDIK-GUARDIAN
Starting Member

20 Posts

Posted - 2009-04-07 : 05:34:45

Also I need the date to be in this format: DD/MM/YYYY instead of YYYY-MM-DD

Here is more of the code, maybe this will help:

Function displayJavaScript()
Dim myJSstr
    myJSstr = "<SCRIPT LANGUAGE=""JavaScript"">" & vbCrlf & "<!--" & vbCrlf
    myJSstr = myJSstr & "function mlc_setdate(pYear, pMonth, pDay, pClose)" & vbCrlf & "{" & vbCrlf
    myJSstr = myJSstr & vbTab & "if (eval(document." & mlcFormName & ".mlcYearList))" & vbCrlf
    myJSstr = myJSstr & vbTab & "{" & vbCrlf
    myJSstr = myJSstr & vbTab & vbTab & "mySelectedIndex = document." & mlcFormName & ".mlcYearList.selectedIndex;" & vbCrlf
    myJSstr = myJSstr & vbTab & vbTab & "if (pYear == 0) pYear = document." & mlcFormName & ".mlcYearList.options[mySelectedIndex].value;" & vbCrlf
    myJSstr = myJSstr & vbTab & vbTab & "mySelectedIndex = document." & mlcFormName & ".mlcMonthList.selectedIndex;" & vbCrlf
    myJSstr = myJSstr & vbTab & vbTab & "if (pMonth == 0) pMonth = document." & mlcFormName & ".mlcMonthList.options[mySelectedIndex].value;" & vbCrlf
    myJSstr = myJSstr & vbTab & "}" & vbCrlf
    myJSstr = myJSstr & vbTab & "document." & mlcFormName & ".mlcYear.value=pYear;" & vbCrlf
    myJSstr = myJSstr & vbTab & "document." & mlcFormName & ".mlcMonth.value=pMonth;" & vbCrlf
    myJSstr = myJSstr & vbTab & "document." & mlcFormName & ".mlcDay.value=pDay;" & vbCrlf
    myJSstr = myJSstr & vbTab & "document." & mlcFormName & ".mlcDate.value="""";" & vbCrlf
    myJSstr = myJSstr & vbTab & "document." & mlcFormName & ".submit();" & vbCrlf
    myJSstr = myJSstr & vbTab & "if (pClose) setInfo(pDay+'/'+pMonth+'/'+pYear);" & vbCrlf
    myJSstr = myJSstr & vbTab & "return(0);" & vbCrlf
    myJSstr = myJSstr & "}" & vbCrlf & vbCrlf
    myJSstr = myJSstr & "//-->" & vbCrlf & "<" & "/SCRIPT>" & vbCrlf
    displayJavaScript = myJSstr
End Function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 10:13:33

how are values coming in date,FirstDate,SecondDate,..? in what format? also i dont think you need to cast it to datetime in insert

SHARDIK-GUARDIAN
Starting Member

20 Posts

Posted - 2009-04-07 : 13:42:43

quote:
Originally posted by visakh16

how are values coming in date,FirstDate,SecondDate,..? in what format? also i dont think you need to cast it to datetime in insert

When it works it uses the same date format as this forum

quote:
04/07/2009 : 05:20:53

I need it to be DD/MM/YYYY

------------------------------------------------------------
Was there ever a trap to match the trap of love?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 13:55:09

then use SET DATEFORMAT dmy before query

SHARDIK-GUARDIAN
Starting Member

20 Posts

Posted - 2009-04-07 : 14:21:58

quote:
Originally posted by visakh16

then use SET DATEFORMAT dmy before query

so it should look like this?



strSql     = "SET DATEFORMAT dmy INSERT INTO tbl_Requests (RequestDate, " & _
							 "GameType, FirstDate, SecondDate, Comments)" & _
        	                 "VALUES (CONVERT(datetime,'" & date & "') , '" SelectedGame & _
            	             "','" & GameType & "', CONVERT(datetime,'" & FirstDate & "'),"  & _
                	         "CONVERT(datetime,'" & SecondDate & "'),'" & Comment & "')"

------------------------------------------------------------
Was there ever a trap to match the trap of love?

SHARDIK-GUARDIAN
Starting Member

20 Posts

Posted - 2009-04-07 : 14:22:55

I'm a n00b :)

------------------------------------------------------------
Was there ever a trap to match the trap of love?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-08 : 03:29:35

quote:

Originally posted by SHARDIK-GUARDIAN

quote:
Originally posted by visakh16

then use SET DATEFORMAT dmy before query

so it should look like this?



strSql     = "SET DATEFORMAT dmy INSERT INTO tbl_Requests (RequestDate, " & _
							 "GameType, FirstDate, SecondDate, Comments)" & _
        	                 "VALUES (CONVERT(datetime,'" & date & "') , '" SelectedGame & _
            	             "','" & GameType & "', CONVERT(datetime,'" & FirstDate & "'),"  & _
                	         "CONVERT(datetime,'" & SecondDate & "'),'" & Comment & "')"

------------------------------------------------------------
Was there ever a trap to match the trap of love?

Did you get error when executing this?

Also formation matters only if you want to show dates in front end application

Always express dates in YYYYMMDD format to wotk with all date settings

Madhivanan

Failing to plan is Planning to fail

SHARDIK-GUARDIAN
Starting Member

20 Posts

Posted - 2009-04-15 : 08:05:20

1,000 thanks for your help, but I gave up and paid someone to fix this. I never expected it would be so difficult.

Have an awesome summer!

------------------------------------------------------------
Was there ever a trap to match the trap of love?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources