How Do I Convert A Unix Date/Time Field To A Date When The The SQL DB Stores That Data As Char 11?
Nov 13, 2007
Hi there.
I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE')
Can anyone out there help?
Please advise. Thanks.
Best.
K7
View 1 Replies
ADVERTISEMENT
Jul 4, 2014
I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.
ALTER PROCEDURE [dbo].[ByDateRange]
@Feed VARCHAR(50),
@CreatedDate DATETIME
select * from Date_table where Create_TM = @CreatedDate
View 1 Replies
View Related
Sep 27, 2005
Hi,
I need to convert a var char field to date field (DD/MM/YYYY)
Current convertion format:
CAST(Report_Date as DATETIME)
How can i convert to Date field with date format of DD/MM/YYYY?
Thanks.
Onn Onn
View 2 Replies
View Related
Nov 28, 2012
Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?
The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.
convert(char(15), process_date,112) as process_dt
View 3 Replies
View Related
Jul 28, 2015
I need to convert Local date time field to GMT. [Does we need to consider daylight saving and so on?]
[URL]
View 5 Replies
View Related
Jun 9, 2006
Hi,We have a lame app that uses 2 datetime(8) fields, 1 stores the date, theother the time.example query:select aud_dt, aud_tmfrom ordersresults:aud_dt aud_tm2006-06-08 00:00:00.000 1900-01-01 12:32:26.287I'm trying to create a query that give me records from the current date inthe past hour.Here's a script that gives me todays date but I cannot figure out the time:select aud_dt, aud_tm, datediff(d,aud_dt,getdate()), datediff(mi, aud_tm,getdate())from orderswhere (datediff(d,aud_dt,getdate()) = 0)results:aud_dt aud_tmdatediff(0=today) timediff (since 1900-01-01)2006-06-08 00:00:00.000 1900-01-01 12:32:26.287 055978689I added this next part to the above query but it does not work since thedate/time is from 1900-01-01and (datediff(mi, aud_tm, getdate()) <= 60)Thanks for any help.
View 2 Replies
View Related
Jun 13, 2002
In SQL Server 2000:
How do I convert a Julian date to a Gregorian date?
How do I convert a Gregorian date to Julian?
Examples please.
Many thanks in advance.
Gary Andrews
View 2 Replies
View Related
May 17, 2015
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
View 3 Replies
View Related
Dec 26, 2014
declare @new table(
id int not null identity(1,1),
dat char(10))
insert into @new
select'111214'
union
select'121214'
select cast(dat as datetime)from @new
-----------------------
2011-12-14 00:00:00.000
2012-12-14 00:00:00.000
but me need results it
2014-12-11
2014-12-12
View 2 Replies
View Related
Aug 7, 2001
What is the easiest way to convert '3/21/1959' to '19590321' I looked at
the CAST and convert and can't come up with a solution.
Thanks
View 3 Replies
View Related
Jan 8, 2013
I have a table in which a date value is stored as varchar.some of these values are stored ina dd/mm/yyyy format and other values are stored in a yyyy-mm-dd format..Now I wish to retrieve some data by querying between two dates. However I need to convert the varchar date value to datetime in order to do this but since the date value is in two different formats, the following doesn't work.
select date_value
from my_table
where CONVERT(DATETIME, date_value, 103) between @date1 and @date2
How can you convert the date value to datetime when its stored in mutiple formats. I can't change the table itself as I dont have admin privelages.
View 14 Replies
View Related
Mar 16, 2014
I want to display only the date part of a date field which contains both date & time information.
For example I have the value '2013-11-14 00:00:00.000' in my result set, and ideally I would like to show only '2013-11-14'.
I have looked up the datepart() command, however I can't work out how to return all parts of the date, rather than just the year, month, or day.
View 3 Replies
View Related
Dec 29, 2005
I have a table named "shift" and I need to setup my query to return only data where the field "startime" = today. The problem I am running into is the starttime field it laid out like "2005-12-29 14:00:00" with different time values. I need to ruturn everything that has todays date regardless of the time value. I tried using GetDate() but that is returning data for other days as well or just data before or after the current time. Does anyone have any suggestions? This is driving me crazy! Thanks, Garrett
View 7 Replies
View Related
Jun 3, 2014
converting date and/or time from character string.Got this error message: "Msg 241, Level 16, State 1, Line 7...Conversion failed when converting date and/or time from character string."
Here's my query:
DECLARE @StartDate AS varchar(30)
DECLARE @EndDate AS varchar(30)
SET @StartDate = (CONVERT(varchar(20),'01-05-2014', 101))
SET @EndDate = (CONVERT(varchar(20),'31-05-2014', 101))
[code]....
View 2 Replies
View Related
Sep 21, 2006
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies
View Related
Dec 30, 2003
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
View 6 Replies
View Related
May 8, 2006
I need to create a sp for sql 2000 to run with Crystal 7. (No I can't upgrade the crystal). The user wants the calendar prompt to be issues when Crystal with the calendar function. The problem is that if I define the sp var to be passed in as a datetime, crystal wants the hh mm ss also. if I issue it as a varchar(10) and convert it in my select it won't bring up the calendar control. Never tried this but I am guessing you can't do a convert on a var you feed from an sp like below
create procedure checkdate @date convert(varchar(10),@date,101)
as
select * from patient_medication where create_timestamp > @date
Any ideas?
View 3 Replies
View Related
May 13, 2004
i have a sql statement that i created in code and it is sending a query to the database
when i dim the variable a datetime variable it says that it cant convert it
if i make the variable a varchar it works but it only returns one result when it should be returning about 10
here is the code
Public Function dbDGQSSearch(ByVal BatchID As String, ByVal CreatedBy As Integer, ByVal CreatedFor As Integer, ByVal DateCreatedMod As Integer, ByVal DateCreated As String, ByVal DateCompletedMod As Integer, ByVal DateCompleted As String, ByVal DateStartedMod As Integer, ByVal DateStarted As String, ByVal SearchType As Integer, ByVal Completed As Integer, ByVal PriorityMod As Integer, ByVal Priority As Integer, ByVal RemainingCallsMod As Integer, ByVal RemainingCalls As Integer, ByVal TotalCallsMod As Integer, ByVal TotalCalls As Integer, ByVal Bonus As Integer, ByVal Keyword1 As String, ByVal Keyword2 As String, ByVal Keyword3 As String, ByVal Keyword4 As String, ByVal Keyword5 As String)
Dim strQueSearch As String
strQueSearch = "SELECT tlkup_Rep.RepID, tlkup_Rep.PositionID, tlkup_Rep.RepFName, tlkup_Rep.RepLName, tlkup_Rep.RepPassword, tlkup_Rep.RepUserName, tlkup_Rep.RepFName + ' ' + tlkup_Rep.RepLName AS RepName, t_Que.QueID, t_Que.BatchID, t_Que.AdminID, t_Que.Manager, t_Que.BonusID, t_Que.QueCompleted, t_Que.QueDate, t_Que.QueNotes, t_Que.QuePriority, t_Que.QueQuantity, t_Que.QueStartDate, t_Que.Mail, t_Que.QueDateComplete, t_Que.QueTotal FROM t_Que INNER JOIN tlkup_Rep ON t_Que.Manager = tlkup_Rep.RepID AND t_Que.Manager = tlkup_Rep.RepID WHERE BatchID<>'' and BatchID<>'2' and BatchID<>'3' and BatchID<>'4' "
'Creates statement for selecting the add to batch data where the criteria appear
If BatchID <> "" Then
strQueSearch = strQueSearch + " and t_Que.BatchID= @BatchID "
End If
If CreatedBy > 1 Then
strQueSearch = strQueSearch + " and t_Que.RepID =@RepID "
End If
If CreatedFor > 1 Then
strQueSearch = strQueSearch + " and t_Que.Manager = @Manager "
End If
If DateCreated <> "" Then
If DateCreatedMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueDate >@QueDate "
ElseIf DateCreatedMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueDate <@QueDate "
ElseIf DateCreatedMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueDate =@QueDate "
End If
End If
If DateCompleted <> "" Then
If DateCompletedMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueDateComplete >@QueDateComplete "
ElseIf DateCompletedMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueDateComplete <@QueDateComplete and t_Que.QueDateComplete >'1/1/1900' "
ElseIf DateCompletedMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueDateComplete =@QueDateComplete "
End If
End If
If DateStarted <> "" Then
If DateStartedMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueStartDate >@QueStartDate "
ElseIf DateStartedMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueStartDate <@QueStartDate "
ElseIf DateStartedMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueStartDate =@QueStartDate "
End If
End If
If SearchType = 0 Then
'Both
'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 "
ElseIf SearchType = 1 Then
'Mail
strQueSearch = strQueSearch + " and t_Que.Mail=1 "
ElseIf SearchType = 2 Then
'Phone
strQueSearch = strQueSearch + " and t_Que.Mail=0 "
End If
If Completed = 0 Then
'Both
'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 "
ElseIf Completed = 1 Then
'Yes
strQueSearch = strQueSearch + " and t_Que.QueCompleted=1 "
ElseIf Completed = 2 Then
'No
strQueSearch = strQueSearch + " and t_Que.QueCompleted=0 "
End If
If Priority > 0 Then
If PriorityMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QuePriority >@QuePriority "
ElseIf PriorityMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QuePriority <@QuePriority "
ElseIf PriorityMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QuePriority =@QuePriority "
End If
End If
If RemainingCalls > 0 Then
If RemainingCallsMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueQuantity >@QueQuantity "
ElseIf RemainingCallsMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueQuantity <@QueQuantity "
ElseIf RemainingCallsMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueQuantity =@QueQuantity "
End If
End If
If TotalCalls > 0 Then
If TotalCallsMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueTotal >@QueTotal "
ElseIf TotalCallsMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueTotal <@QueTotal "
ElseIf TotalCallsMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueTotal =@QueTotal "
End If
End If
If Bonus > 1 Then
strQueSearch = strQueSearch + " and t_Que.BonusID =@BonusID "
End If
If Keyword1 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword1+'%' "
End If
If Keyword2 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword2+'%' "
End If
If Keyword3 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword3+'%' "
End If
If Keyword4 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword4+'%' "
End If
If Keyword5 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword5+'%' "
End If
'makes statement into sqlcommand
C.daQueSearch.SelectCommand.CommandText = strQueSearch
'var declaration
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@BatchID", SqlDbType.VarChar, 12))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Manager", SqlDbType.Int))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@RepID", SqlDbType.Int))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueDate", SqlDbType.VarChar, 20)) '<--- This is what,when i change to datetime, says it cant convert
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueStartDate", SqlDbType.VarChar, 20))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueDateComplete", SqlDbType.VarChar, 20))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QuePriority", SqlDbType.Int))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueQuantity", SqlDbType.BigInt))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueTotal", SqlDbType.BigInt))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@BonusID", SqlDbType.SmallInt))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword1", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword2", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword3", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword4", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword5", SqlDbType.VarChar, 50))
'data entry
C.daQueSearch.SelectCommand.Parameters("@BatchID").Value = BatchID
C.daQueSearch.SelectCommand.Parameters("@Manager").Value = CreatedBy
C.daQueSearch.SelectCommand.Parameters("@RepID").Value = CreatedFor
C.daQueSearch.SelectCommand.Parameters("@QueDate").Value = DateCreated
C.daQueSearch.SelectCommand.Parameters("@QueStartDate").Value = DateStarted
C.daQueSearch.SelectCommand.Parameters("@QueDateComplete").Value = DateCompleted
C.daQueSearch.SelectCommand.Parameters("@QuePriority").Value = Priority
C.daQueSearch.SelectCommand.Parameters("@QueQuantity").Value = RemainingCalls
C.daQueSearch.SelectCommand.Parameters("@QueTotal").Value = TotalCalls
C.daQueSearch.SelectCommand.Parameters("@BonusID").Value = Bonus
C.daQueSearch.SelectCommand.Parameters("@Keyword1").Value = Keyword1
C.daQueSearch.SelectCommand.Parameters("@Keyword2").Value = Keyword2
C.daQueSearch.SelectCommand.Parameters("@Keyword3").Value = Keyword3
C.daQueSearch.SelectCommand.Parameters("@Keyword4").Value = Keyword4
C.daQueSearch.SelectCommand.Parameters("@Keyword5").Value = Keyword5
Try
C.ndConnection.Open()
C.daQueSearch.SelectCommand.ExecuteNonQuery()
Catch ex As Exception
lblMainError1.Text = err("dbDGQSSearch " + ex.Source, ex.Message, CurUsr)
lblMainError1.Visible = True
Finally
C.ndConnection.Close()
End Try
FillQSDG()' this fills the datagrid
End Function
View 8 Replies
View Related
Apr 16, 2004
Hi,
Can anyone tell me how to convert date and time to string.
I got one field inside my database which contain data like below:
4/16/2004 10:19:01 AM
if i wan to call out a record which have to refer to the data above
Select *
From table_A
where field_date= ???????
what to fill in the ??????
Thanx
View 3 Replies
View Related
Jul 20, 2005
Precisely, here's what I need:When I run getdate(), I get, for example:August 9 2004 5:17 P.M.I want to turn the date portion into:8/9/2004 format and update one column with itI want to turn 5:17 P.M. into:hhmmss and update another column with it.I've been playing around with datepart, with substr, with you name it,and I'm stumped.Any code samples, other help most appreciated.Thanks,Google Jenny*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jun 18, 1999
I have a text file like this :
K045|13/03/1997
When I upload this text file to SQL Server 6.5, with the following fields :
Code Chraracter 4
Tdate Smalldatetime
I use BCP to upload this text file to SQL tables, but I got a wrong date,
like :
Text file :
K045|13/03/1997
SQL Tables data :
K045 Nov 19 1935 7:00PM
Can anyone on the net can help me ?
Thank's
View 1 Replies
View Related
Aug 9, 2006
I have a field that I would like to only display the date as mm/dd/yyyy. Current field shows mm/dd/yyyy hh:mm:ss AM.
View 10 Replies
View Related
Jul 27, 2004
Im working with a breaking system and I wont to convert the (FROM) datetime column to accept just time like (4:00:00 AM) without the date (7/23/2004) but it doesn’t have column format like the access ??
I found something in the SQL help :
How to convert the format of a Date Time String transformation (Enterprise Manager)
To convert the format of a Date Time String transformation
1.On the Transformations tab of the Transform Data Task Properties or Data Driven Query Task Properties dialog box, click the Source column containing the date or time to be modified, and then click the Destination column where you want the modified string to be placed.
2.Do one of the following:
•If there is a mapping arrow connecting the two columns, click Delete, and then click New.
•If there is no mapping arrow, click New.
3.In the Create New Transformation dialog box, click DateTime String.
4.Click the General tab, and then click Properties.
5.In the Date Format list, select the format you want.
6.Click Naming to display the Calendar Names dialog box, where you can select long or short day or month names and the A.M. and P.M. designators you want.
7.In the Language list, select the language you want, and then click Set Language Defaults.
But unfortunately I didn't find the "Transformations tab" I look a lot in the SQL Enterprise Manager
Do anybody work with the Transformations or at least know where is it please ???
View 1 Replies
View Related
Dec 21, 2014
I have a standard datetime and I need to convert it to the client specification of:
YYYY-MM-DDThh:mm:ssTZD
eg: 2009-04-16T19:20:30+08:00
I am not sure of the easiest way to do this.
The test code below gets me part of the way but I am unsure on how to get the offset on the end without hardcoding to much.
DECLARE @datetime DATETIME = '2014-12-20 12:30:00'
SELECT CONVERT(VARCHAR(30),@datetime,127)
View 2 Replies
View Related
Aug 3, 2015
We have 2 columns:
StartDate - data type Date
StartTime - data type Time
I need to combine them into a DateTime data type. For now, I convert each of them into varchar, insert space in between, and convert to DateTime, like this:
convert(datetime, convert(varchar(10), EndDate , 101) + ' ' + CONVERT(varchar(20), EndTime,24))
Is there a better, more elegant way to do this?
View 9 Replies
View Related
Oct 27, 2015
I am importing data from Dynamics CRM online using ODATA in Power Query. All datetimes in the database is stored in UTC and I need to convert these into CET. But it's not as simple as just to add 1 hour to the datetime from the database because due to Daylight. Savings time on half the dates I need to add 2 hours - how can I do this?
View 3 Replies
View Related
Dec 9, 2005
Dear Frends
Iam using SQL Server 2000
Is there any way to use Date time field to store value before the Date 01-Jan-1753?
Thanks
View 6 Replies
View Related
May 4, 2006
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
View 3 Replies
View Related
Nov 11, 2013
I want to update the time in a datetime field with the current time.
Fields current value is:
2013-11-11 00:00:00.000
I want to insert this into another table and when I do I want to grab the current time and update that field.
field name: picked_dt
Table: oeordlin
or is there a way through sql to update the time when the picked_dt is updated?
View 2 Replies
View Related
Apr 3, 2008
I have two fields DSRHADTI which is an isodate and DSRHTIME which is 8 char time field in format 10.31.00. I want to take both these fields and put them into a field that is database timestamp so I have converted DSRHDATI to 10 character field. I am then trying to use substring to put both into 18 character field using derived column transformation editor. but it does not like the below. It's red syntax error what am I missing.
(SUBSTRING(Copy of DSRHDATI,1,4) +' /' + SUBSTRING( Copy of DSRHDATI,6,2) + '/ ' + SUBSTRING(Copy of DSRHDATI,9,2)) + SUBSTRING(DSRHTIME,1,2) + '.' + SUBSTRING(DSRHTIME,4,2) + '.' + SUBSTRING(DSRHTIME,7,2)
One I get the above to work I plan on convert 18 char to datetimestamp.
Am I on the right track on how to do this?
View 16 Replies
View Related
Feb 25, 2008
Hi,
I have report scheduled in a SSRS. SSRS creates a job and from MSDB.DBO.SysJobActivity the
Next_Scheduled_Run_Date column provides the next execution date of the report.
Can any one tell me what is the date format that SSRS stores is it a getdate() or a UTC Date??
View 1 Replies
View Related
Jan 17, 2002
I'm adding up quantities of an item that are entered in one after another, so the date is the same, but the time differs.
I used the "first" function in access which works the way I want, but sql7 doesn't use the same function. The "convert" function does not work in access but Here is the gist of the query:
SELECT first(CONVERT(varchar,transactions.tran_date,101)) AS [trandate], transactions.tran_type,
SUM(transactions.qty) AS totqty,...etc.
thanx,
Herb
View 1 Replies
View Related
Nov 3, 1999
I've developed a system that uses SQL Server as the DB backend. I'm having problems saving date/time fields from SOME- not all, workstations. The workstations I'm having problems with are all NT WORKSTATION 4.0, and the
native OS language is portuguese. The server is NT SERVER 4.0, and the native OS language is english. All machines have the same international settings,
portuguese(Brazilian). SQL Server is english version 6.5.
I've been using the SQL Trace utility to track what's been going on, and I
know that on the NT WORKSTATION machines, the dates get sent to the SQL Server in portuguese format, whereas on the other machines, it gets properly sent, in english format. I've read the booksonline, but just don't seem to figure out what and where to configure. Can anyone shed some light into this problem, ie, HOW EXACLTY TO CONFIGURE client workstations in order to get date/time problems solved?
View 1 Replies
View Related
