• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

Day month and year format.

i have the following SQL string as part of my VB2005 code.

Select flight, min(DepDT) as DepDate,  convert(varchar(5), DateAdd(s,Sum(datediff(s,depdt,arrdt)),0),114) As FlyingTime, Substring(convert(varchar,dateadd(s,datediff(s,min(depdt),Max(arrdt)),0),120),9,20) As ElapsedTime From (Select flight,depdate + deptime As DepDT, arrdate+arrtime As ArrDT from legplanning Where Aircraft = '" + lblaircraft(k).Text + "' AND DepDate + deptime >='" + System.DateTime.Now.AddDays(-1) + "' AND DepDate + deptime <= '" + System.DateTime.Now.AddDays(14) + "') legplanning Group By flight

The problem i have is the System.DateTime.Now.AddDays(14) and the System.DateTime.Now.AddDays(-1) needs to be formatted as dd,mmmm,yyyy

Could somebody please show me the correct code for this i.e edit the sql string for me?
0
dr_dudd
Asked:
dr_dudd
  • 7
  • 5
  • 3
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
Hi dr_dudd,

You better format in ISO format. You can use:
System.DateTime.Now.AddDays(-1).TosTring("yyyy-MM-dd")


Cheers!
0
 
dr_duddAuthor Commented:
hmmm.... tried replacing '" + System.DateTime.Now.AddDays(-1) + "' in the sql string with '"+System.DateTime.Now.AddDays(-1).TosTring("yyyy-MM-dd")+"'

and nothing was returned from the sql server. :-(
0
 
Éric MoreauSenior .Net ConsultantCommented:
dr_dudd,

what if you hardcode a date into your string to test if your string is working correctly?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Éric MoreauSenior .Net ConsultantCommented:
dr_dudd,

Wait a minute. What is this?
> DepDate + deptime

What are the datatype of these 2 fields? Can you do that?
0
 
dr_duddAuthor Commented:
If i hardcode a date/time into my code then it works fine.... eg. 15 - november-2006
0
 
Éric MoreauSenior .Net ConsultantCommented:
dr_dudd,
 what if you put 2006-11-15 ?
0
 
dr_duddAuthor Commented:
Comment from emoreau
Date: 11/23/2006 12:24PM GMT
      Comment       Accept

dr_dudd,
 what if you put 2006-11-15 ?



Returns rows correctly...
0
 
SanclerCommented:
Looks like a typo in this

System.DateTime.Now.AddDays(-1).TosTring("yyyy-MM-dd")

Shouldn't TosString be ToString?  Or is it something I haven't come across?

Roger
0
 
Éric MoreauSenior .Net ConsultantCommented:
dr_dudd,
> Returns rows correctly...

so System.DateTime.Now.AddDays(-1).TosTring("yyyy-MM-dd")

will work correctly. Can you whow your code?

or maybe you should try: System.DateTime.Date.AddDays(-1).TosTring("yyyy-MM-dd")

0
 
dr_duddAuthor Commented:
Heres a copy of the code for getting the data from my sql server and drawing appropriate boxes on screen.


For k = 1 To 5
            Dim flightlength As String = "Select flight, min(DepDT) as DepDate,  convert(varchar(5), DateAdd(s,Sum(datediff(s,depdt,arrdt)),0),114) As FlyingTime, Substring(convert(varchar,dateadd(s,datediff(s,min(depdt),Max(arrdt)),0),120),9,20) As ElapsedTime From (Select flight,depdate + deptime As DepDT, arrdate+arrtime As ArrDT from legplanning Where Aircraft = '" + lblaircraft(k).Text + "' AND DepDate + deptime >='" + System.DateTime.Now.AddDays(-1) + "' AND DepDate + deptime <= '" + System.DateTime.Now.AddDays(14) + "') legplanning Group By flight"
            Dim getflight1 As New SqlCommand(flightlength, cnafm)
            Try
                cnafm.Open()
                Dim flight1 As SqlDataReader = getflight1.ExecuteReader
                With flight1
                    If .HasRows Then
                        While .Read
                            days = .GetString(3)
                            daysint = days.Substring(0, 2)
                            daysint = ((daysint * 24) * 6)
                            hours = .GetString(3)
                            hoursint = hours.Substring(3, 2)
                            hoursint = (hoursint * 6)
                            minutes = .GetString(3)
                            minutesint = minutes.Substring(6, 2)
                            minutesint = (minutesint / 10)
                            length = daysint + hoursint + minutesint - 144
                            pos1 = .GetSqlDateTime(1)
                            Convert.ChangeType(pos1, TypeCode.DateTime)
                            pos2 = DateDiff(DateInterval.Minute, now, pos1)
                            position = 190 + (pos2 / 10)
                            formGraphics.FillRectangle(BlackBrush, New Rectangle(position, 131 + (k * 182 - 182), length, 20))
                        End While
                    End If
                End With

            Catch ex As Exception

            Finally
                cnafm.Close()
            End Try
        Next k
0
 
SanclerCommented:
Doh.  I thought I was just being ignored, so I looked more carefully.  And I see the "typo" was TosTring not TosString.

Sorry for butting in.

Roger
0
 
dr_duddAuthor Commented:
The answer was so simple its annoying...... just add .tolongdatestring to the end of the system.datetime.now.adddays(-1)
0
 
Éric MoreauSenior .Net ConsultantCommented:
dr_dudd,

change datetime for date and add the ToString:

>             Dim flightlength As String = "Select flight, min(DepDT) as
> DepDate,  convert(varchar(5), DateAdd(s,Sum(datediff(s,depdt,arrdt)),0),114)
> As FlyingTime, Substring(convert(varchar,dateadd(s,datediff(s,min(depdt),Max(arrdt)),0),120),9,20)
> As ElapsedTime From (Select flight,depdate + deptime As DepDT,
> arrdate+arrtime As ArrDT from legplanning Where Aircraft = '" +
> lblaircraft(k).Text + "' AND DepDate + deptime >='" +
> System.Date.Now.AddDays(-1).tostring("yyyy-MM-dd") + "' AND DepDate + deptime <= '" +
> System.Date.Now.AddDays(14).tostring("yyyy-MM-dd") + "') legplanning Group By flight"
0
 
Éric MoreauSenior .Net ConsultantCommented:
I think I deserve the points.
0
 
SanclerCommented:
I agree that emoreau should get the points

Roger
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 7
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now