Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Merge 3 date fields into one via a query

I have a asp form that submits fields to a SQL database. One of the fields is a date field split into 3 drop downs (day, month and year). On submit these fields populate day, month year field in my MS SQL database.

1. How can i merge these 3 fields via a query into a standard date format dd/mm/yyyy

2. how can I then count the number of days passed from this date to the current date ?


0
coevans1
Asked:
coevans1
  • 5
  • 2
  • 2
  • +1
1 Solution
 
ZhongYuCommented:

If you want to do the processing on the SQL server, do like this:

create procedure submitform
  @year char(4),
  @month char(2),
  @day char(2)
AS
  DECLARE @date datetime
  DECLARE @daydiff int

  SELECT @date = @year + '-' + @month + '-' + @day
  SELECT @daydiff = datediff(dd, @date, getdate())
 
 .... <your code>
0
 
krispolsCommented:
1/
First, you must check that the date the user select is correct. Otherwise you get a database error.
Second you call your store procedure with the 3 fields as params.
Your sp do a think like that:

insert into table (datefield) values (cast(@DAY as varchar) + '/' + cast(@MONTH as varchar) + '/' + cast(@YEAR as varchar))

2/
use DATEDIFF function
0
 
coevans1Author Commented:
not sure I follow 100%. The main body of my query follows:-
SELECT DISTINCT
                      Issues.NCR, Issues.Name AS Originator, Issues.Details, Owners.Owner AS [Primary Owner], progress.Owner AS [Assigned Owner], Issues.Source,
                      Issues.Technology, Issues.Deliverable, Issues.Product AS [Function], Issues.Affected AS Attribute, Issues.project AS [Project ID],
                      Owners.Country AS [TED Location]
FROM         Issues LEFT OUTER JOIN
                      progress ON Issues.NCR = progress.NCR LEFT OUTER JOIN
                      Owners ON Issues.Areaofworld = Owners.Country AND Issues.Affected = Owners.Affected
WHERE     (Issues.Control LIKE 'extern%' OR
                      Issues.Control IS NULL) AND (Issues.Control LIKE 'inter%' OR
                      Issues.Control IS NULL) AND (progress.Closed LIKE 'no' OR
                      progress.Closed IS NULL)

I have 3 Columns in the Issues database (day_incident, Month_incident & Year_incident) how do I go about polling these into 1  date field ??

Thanks :)

In recognition of my ineptitude i have upped the points on offer :)

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
coevans1Author Commented:
not sure I follow 100%. The main body of my query follows:-
SELECT DISTINCT
                      Issues.NCR, Issues.Name AS Originator, Issues.Details, Owners.Owner AS [Primary Owner], progress.Owner AS [Assigned Owner], Issues.Source,
                      Issues.Technology, Issues.Deliverable, Issues.Product AS [Function], Issues.Affected AS Attribute, Issues.project AS [Project ID],
                      Owners.Country AS [TED Location]
FROM         Issues LEFT OUTER JOIN
                      progress ON Issues.NCR = progress.NCR LEFT OUTER JOIN
                      Owners ON Issues.Areaofworld = Owners.Country AND Issues.Affected = Owners.Affected
WHERE     (Issues.Control LIKE 'extern%' OR
                      Issues.Control IS NULL) AND (Issues.Control LIKE 'inter%' OR
                      Issues.Control IS NULL) AND (progress.Closed LIKE 'no' OR
                      progress.Closed IS NULL)

I have 3 Columns in the Issues database (day_incident, Month_incident & Year_incident) how do I go about polling these into 1  date field ??

Thanks :)

In recognition of my ineptitude i have upped the points on offer :)

0
 
coevans1Author Commented:
this is the asp code if this helps ?



sSQLInsertString = "insert into issues (" & _
                                     "name,day_incident,month_incident,year_incident,customer,type,product,country,day_raised," & _
                                     "month_raised,year_raised,technology,region,affected,ref,source,sr,control,areaofworld,project,deliverable,details) " & _
                                     "Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
                    
                  ' Connect the database connection to the object
                  Set Command.ActiveConnection = sConString
                                  
                  ' Define the SQL insert statement
                  Command.CommandText = sSQLInsertString
                  Command.Prepared = True
                  
                  ' Define the SQL Parameters
                  Command.Parameters.Append _
                    Command.CreateParameter("name",200, ,255)
                  Command.Parameters.Append _
                    Command.CreateParameter("day_incident",3)
                  Command.Parameters.Append _
                    Command.CreateParameter("month_incident",3)
                  Command.Parameters.Append _
                    Command.CreateParameter("year_incident",3)
                  Command.Parameters.Append _
                    Command.CreateParameter("customer",200, ,255)
                  Command.Parameters.Append _
                    Command.CreateParameter("type",200, ,255)
                  Command.Parameters.Append _
                    Command.CreateParameter("product",200, ,255)
                  Command.Parameters.Append _
                    Command.CreateParameter("country",200, ,255)
                  Command.Parameters.Append _
                    Command.CreateParameter("day_raised",3)
                  Command.Parameters.Append _
                    Command.CreateParameter("month_raised",3)
                  Command.Parameters.Append _
                    Command.CreateParameter("year_raised",3)                  
                  Command.Parameters.Append _
                    Command.CreateParameter("technology",200, ,255)
                  Command.Parameters.Append _
                    Command.CreateParameter("region",200, ,255)
                  Command.Parameters.Append _
                    Command.CreateParameter("affected",200, ,255)
                  Command.Parameters.Append _
                    Command.CreateParameter("ref",129, ,10)
                  Command.Parameters.Append _
                    Command.CreateParameter("source",200, ,255)
                  Command.Parameters.Append _
                    Command.CreateParameter("sr",200, ,255)
                  Command.Parameters.Append _
                    Command.CreateParameter("control",200, ,255)
                   Command.Parameters.Append _
                    Command.CreateParameter("areaofworld",200, ,255)
                   Command.Parameters.Append _
                    Command.CreateParameter("project",200, ,255)
                  Command.Parameters.Append _
                    Command.CreateParameter("deliverable",200, ,255)
                  Command.Parameters.Append _
                    Command.CreateParameter("details",200, ,8000)
                  
                                                  
                  ' Information entered by the user is added to the database
                  Command("name") = Request.Form.Item("Name")
                  Command("day_incident") = CInt(Request.Form.Item("Day"))
                  Command("month_incident") = CInt(Request.Form.Item("Month"))
                  Command("year_incident") = CInt(Request.Form.Item("Year"))
                  Command("customer") = Request.Form.Item("Customer")
                  Command("Type") = Request.Form.Item("IssueType")
                  Command("product") = Request.Form.Item("Source")
                  Command("country") = Request.Form.Item("Country")
                  Command("day_raised") = CInt(Request.Form.Item("DayRaised"))
                  Command("month_raised") = CInt(Request.Form.Item("MonthRaised"))
                  Command("year_raised") = CInt(Request.Form.Item("YearRaised"))
                  Command("technology") = Request.Form.Item("Technology")
                  Command("region") = Request.Form.Item("Region")
                  Command("affected") = Request.Form.Item("AffectedAttribute")
                  Command("ref") = Request.Form.Item("Ref")
                  Command("source") = Request.Form.Item("SourceOrigin")
                  Command("sr") = Request.Form.Item("SR")
                  Command("control") = Request.Form.Item("ControlPoint")
                  Command("areaofworld") = Request.Form.Item("area")
                  Command("project") = Request.Form.Item("Project")
                  Command("deliverable") = Request.Form.Item("Deliverable")                  
                  Command("details") = Request.Form.Item("DetailedDescription")
                                    
                ' Response.Write (sSQLInsertString)            
                  ' Execute the command
                  Command.Execute  
                  
                  %>
0
 
troutaCommented:
If I understand correctly what you are looking for, to get the date you would

convert (datetime, month_incident + '/' + day_incident + '/' + year_incident) as date1

and then to get the days difference, it would be something like

datediff( dd, convert (datetime, month_incident + '/' + day_incident + '/' + year_incident, getdate()) as Date_Diff

so your query would look like
SELECT DISTINCT
                     Issues.NCR, Issues.Name AS Originator, Issues.Details, Owners.Owner AS [Primary
Owner], progress.Owner AS [Assigned Owner], Issues.Source,
                     Issues.Technology, Issues.Deliverable, Issues.Product AS [Function], Issues.Affected,
                     convert (datetime, month_incident + '/' + day_incident + '/' + year_incident) as date1,
                     datediff( dd, convert (datetime, month_incident + '/' + day_incident + '/' + year_incident, getdate()) as Date_Diff

 
AS Attribute, Issues.project AS [Project ID],
                     Owners.Country AS [TED Location]
FROM         Issues LEFT OUTER JOIN
                     progress ON Issues.NCR = progress.NCR LEFT OUTER JOIN
                     Owners ON Issues.Areaofworld = Owners.Country AND Issues.Affected = Owners.Affected
WHERE     (Issues.Control LIKE 'extern%' OR
                     Issues.Control IS NULL) AND (Issues.Control LIKE 'inter%' OR
                     Issues.Control IS NULL) AND (progress.Closed LIKE 'no' OR
                     progress.Closed IS NULL)
0
 
troutaCommented:
Oops, the query should be

SELECT DISTINCT
                    Issues.NCR, Issues.Name AS Originator, Issues.Details, Owners.Owner AS [Primary

Owner], progress.Owner AS [Assigned Owner], Issues.Source,
                    Issues.Technology, Issues.Deliverable, Issues.Product AS [Function], Issues.Affected


AS Attribute, Issues.project AS [Project ID],
                    Owners.Country AS [TED Location],
                    convert (datetime, month_incident + '/' + day_incident + '/' + year_incident) as
date1,
                    datediff( dd, convert (datetime, month_incident + '/' + day_incident + '/' + year_incident,
getdate()) as Date_Diff

FROM         Issues LEFT OUTER JOIN
                    progress ON Issues.NCR = progress.NCR LEFT OUTER JOIN
                    Owners ON Issues.Areaofworld = Owners.Country AND Issues.Affected = Owners.Affected
WHERE     (Issues.Control LIKE 'extern%' OR
                    Issues.Control IS NULL) AND (Issues.Control LIKE 'inter%' OR
                    Issues.Control IS NULL) AND (progress.Closed LIKE 'no' OR
                    progress.Closed IS NULL)
0
 
krispolsCommented:
hello coevans1,

toutra is right, but as I said before, check you date before use the command object to insert it in the database. what's append if the user select the 02/31/2001?
0
 
coevans1Author Commented:
Exactly what I was after, u missed the ) in the datediff statement tho :),

Cheers
0
 
coevans1Author Commented:
Exactly what I was after, u missed the ) in the datediff statement tho :),

Cheers
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now