Here is my scenario:
I want to build a query that takes the max (or most current) DateAtMidnight from a table with all fields. This query qould ultimately be used for SSIS packages instead of formulating variables and other tables.
For simplicity, I started with a basic query.
Select DateAtMidnight, Field1, Field2
From Sales_Invoices
I cannot find a good example of how to use getdate() -1 day. Please assist.
Am using the getdate() function to insert dates to a table in a database, the date are inserted as for example '22/10/2006 03:05:56 AM'... I used to retrieve rows with specific dates using C# with a query like:
"SELECT * from log WHERE log_date BETWEEN '" + dtCurrentDate.String() + "' AND '" + DateTime.Now().ToString() + "'"
when I moved to VB .NET the same query returned no results, I thought this is coz VB .NET shows the same date as '10/22/2006 03:05:56 AM'
so I changed the formatting in the query but still no result
I tried use the same query in SQL Server 2005 Query Window
SELECT * FROM log WHERE log_date BETWEEN '22/10/2006 03:00:00 AM' AND '22/10/2006 04:00:00 AM'
still no result even the following query returned no result
SELECT * FROM log WHERE log_date = '22/10/2006 03:05:56 AM'
I tried to use the cast and convert functions but it couldn't convert the values between quotation to date
in the end i used the query: SELECT getdate() to see how dates are represented, the result was as for example
2006/10/22 03:05:56 AM
any help??
hi allcan somebody tell me how can i insert todays date in sqlserver 2000 in dd/mm/yyyy format...i knw we use getdate but how exactly..plz write the code for me...i have datetime as datatype for my column name.....i want it in this format because i am using a datetimepicker to compare values while retreiving the data..and this datetime picker is taking dd/mm/yyyy format so i want to insert records in that format...plz helpthanks in advance
View 12 Replies View RelatedHi
When I perform the following sql
DECLARE @HOLD CHAR(20)
select @hold = getdate()
select getdate()
select @hold
I get different results
------------------------------------------------------
2006-09-06 15:21:58.657
(1 row(s) affected)
--------------------
Sep 6 2006 3:21PM
(1 row(s) affected)
The reason I see is the assignment to a variable of a different data type. Is there a way to preserve the date format when I assign the value to a character field?
Thanks,
Does getdate() always returns date values in a standard format or it changes with the system date settings. I want to get 200110 for october 2001 and want to use getdate() for this purpose.
Thanks
Rajesh
How can I convert value from getdate() function to following format?
2006-06-20 00:00:00
2006-06-20 10:25:55.063 to 2006-06-20 00:00:00
Thanks
Sanjeev Shrestha
12/17/1971
Hi,
Getdate() returns the following format :
Apr 21 2008 4:47PM
How can i obtain '21-04-2008' format, stripped from time.... ?
I have a weird error that just started showing up. This process has run many times before and just today it started erroring.
The error I get is:
There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.
I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".
Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.
Hello,
I'm using MS SQL Server 7.
How would I use the getdate function to update a field called RunDate to the following format?:
yymmdd
Thanks,
Denise
I'm trying to return the month part of the current date, along with the month number. I want it to return:
01 - January
02 - February, etc
It's fine for October to December, but for the other nine months my code returns 1 - January and I want to be sure the leading 0 is added. How can I do this? Here is my current code:
CONVERT(nvarchar,MONTH(Getdate())) + ' - ' + DATENAME(MONTH,Getdate()) as MonthName
,convert(varchar,getdate(),101) as [CONFIRMATION_DATE!1!REPORT_DATE]
The above displays as 8/26/2006, anyway you can convert that to a long format in the SP?
I.E. August 26, 2006
Thanks.
How can i return month in the format of 'April'.
When i am trying select month(getdate()) i am getting 4 but i am looking for text format - April.
Please correct format.
Thanks
Dk
When you converting varchar to date time
SELECT convert(varchar,getdate())
What is the getdate here
i.e. we are converting any value to today's date
With this clause "select convert(varchar(16), getdate(), 101)" I can get mmddyyyy but
How can I get time such as mmddyyyyhhmm
Thanks
Daniel
e.g.
1st March 2005 12:00:00
is showing as
01/03/2005 00:00:00
instead of
01/03/2005
Why does this happen?
I have date coming to one page as a string in the following format"May 4 2005 12:00AM"
I need to query one of my tables using this date in combination of other nondate values. How can I convert this date into valid sql server datetime format before I query a database tables
Please help
Hi,
I am trying to access a date column up to millisecond precession. So I cast date to as follows:
Code BlockCONVERT(varchar(23),CREATE_DATE,121)
I get millisecond part as a result of query but its 000?.
When I try to test the format by using getDate instead of DateTime column I get right milliseconds.
CONVERT(varchar(23),GetDate(),121) --Gives right milliseconds in return
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.
Hello all,
I'm trying to write a query against an exisiting table that i can't modify and i'm running into a bit of a problem. The table stores timestamps as a char field instead of a datetime.
So, i've had to use the CONVERT function to change it to a datetime during my query. A sample is below:
SELECT convert(datetime, logged, 120) FROM AP200310
This works, except i want to include the option of querying a single day. Since the data that is returned is in this format:
12/12/2006 6:54:15 PM
The following sql statement doesn't work:SELECT convert(datetime, logged, 120) FROM AP200310 WHERE logged = '12/12/2006'
Thanks in advance for any help.
Hi, I wanted to take a date from my Sql server. it is save as dd/mm/yyyy. but when i use the select command it return me dd-mm-yyyyT00:00:00.0000000+08:00. i try to use this following code but it is not working....thedate = String.Format("{0:dd/MM/yyyy}", (reader.GetSqlDateTime(1)))
here is my complete codePublic Function deleteOrder(ByVal oid As Integer) As String
conn = dbCon.getConnection()
Dim cmd1 As New SqlCommand
Dim reader As SqlDataReader
Dim valid, sendDate, sd As String
Dim thedate As String
Dim cancelPeriod As String = CStr(System.DateTime.Today.AddDays(+3))
cmd1.CommandText = "select orderSendDate " & _
"from orders " & _
"where orderID = @oid"
cmd1.Parameters.Add("@oid", oid)
cmd1.Connection = conn
conn.Open()
reader = cmd1.ExecuteReader()
If Not reader.HasRows Then
valid = "No match found"
Else
If reader.Read Then
'String.Format("{0:dd/MM/yyyy}",(dr.GetSqlDateTime(1)));
thedate = String.Format("{0:dd/MM/yyyy}", (reader.GetSqlDateTime(1)))
'sd = reader("orderSendDate").ToString
'sendDate = sd.Substring(0, 10)
conn.Close()
End If
End If
'Return errMsg
If thedate = Convert.ToDateTime(cancelPeriod) Or thedate < CStr(System.DateTime.Today.AddDays(+3)) Then
valid = "You are not allowed to change"
Else
Try
conn.Open()
Dim strUpdate As String
Dim cmd As New SqlCommand
strUpdate = "update orders set orderStatus = @os where orderID = @oid"
cmd.Parameters.Add("@os", "c")
cmd.Parameters.Add("@oid", oid)
cmd.CommandText = strUpdate
cmd.Connection = conn
cmd.ExecuteNonQuery()
conn.Close()
valid = "Cancellation succesful"
updateOrderItemStatus(oid)
Catch ex As Exception
'Response.Write(ex.Message)
errMsg = ex.Message
conn.Close()
End Try
End If
Return valid
End Function
When i call my web method it gives me an error saying that page is not found... please teach me how to convert it.
thx
Datetime for using SQLSERVER QUERY NOT ANOTHER
MM/DD/YYYY HH:MM:SS AM/PM format using only sql query not using SUBSTRING ANY IDEAS
????
regards
sadeesh
I have installed the trial version of windows server 2003 on the second hard drive on my computer. I set up IIS and ran my website on it but the problem is when I do something on the site, which has a sql insert statement regarding datetime.now it says, "conversion failed when converting datetime from character string"
I think it's to do with the clock on server 2003, the format is like: 11/07/2007 2:39:59 a.m.
I think it should be in format AM and not a.m.
Any ideas on how to change the time format on a computer?
Or should I just change the Columns in my table to a Nvarcher value or something?
thanks
Hello:
Hello:
I have some code in a asp.net function in C# like this:
DateTime datetime = DateTime.Now;
SqlCommand CommandEvent = new SqlCommand("spAddNewEvents", Connection);
CommandEvent.Transaction = Trans;
CommandEvent.CommandType = CommandType.StoredProcedure;
*I try First : CommandEvent.Parameters.Add("@date", datetime);
*I try Secound: CommandEvent.Parameters.Add("@date", SqlDbType.DateTime,
8).Value = datetime;
and have some storeprocedure with this code
CREATE PROCEDURE dbo.spAddEvents
(
@guid uniqueidentifier,
@language char (2),
@date as datetime,
@eventId as varchar (50),
@userid as varchar (20)
)
AS
execute('insert into tblEvents'+@language+'( guid, [date], [id], userid)
values('''+@guid+''','''+@date+''','+@eventId+','''+@userId+''')')
GO
The problem is when I try to insert a new event. The event insert are fine,
but the datetime's secound in tblEvents always is 00, and I check the
datetime variable to insert and have secound different that 00.
the table definition is Data Type: datetime and Length: 8, how must be?
I run Profiler and a I get this:
exec spAddEvents @Guid = 'C879D062-C268-4A3E-8D58-1937B7612EC2', @language = N'ES', @date = 'May 6 2004 11:29:58:140PM', @eventId = 6, @userid = N'anibal'
Best regards.
Owen.
Hi,I'm new to SQL Server (Express) and I wonder if there is a way that I can format a date's appearance in the database, that is, the format of the datetime column.
When I view a date in VWD Express, it's in my country's format (2006-11-24 for example) but when I try to insert a date using the same format using a web form, the inserted date in the database becomes 1905-06-something. This happens regardless of whether I'm inserting a string or if the string has been converted to a date via CDate.
So, is there a way I can set the database's date format? And why is it wrong anyway? It's bugging me as the original (Swedish) date is already in the ISO format that SQL Server seems to use (such as yyyy-mm-dd), and I'm using localhost with Windows set to Swedish, IE 6 set to Swedish, and even web.config's UICulture and Culture to Swedish as well.
Of course, I can rearrange the order of the date's numbers to get proper values in the db, but it seems as an unnecessary step and I can't figure out what format to use anyway.
All help is very welcome.
Pettrer
Hi There,
I'm in a learning phase of SQL server 7. I need your help for the following:
I am selecting from a dropdown list Month ('January, february,..... but not as '01', '02'..) and Year (2000, 2001.....), when they are selected a table is displayed for next 12 months starting from the selected month (like say, if April and 2002 was selected, my other table will show Apr. 02 - Mar. 03 ), and data is inserted into it manually.
My question is How to insert this date into the sql database...as I just need the month and the year.....and in sql server the date datatype is "datetime", also i have only one column for this month and year....should i change my tables and make different columns for Month and Year. Can only Month (Januray, february.....) and Year (2000, 2001...) be inserted.
Dear Folks,
how can i know the datetime format of my current session? and is it possible to change that to required format permanently for that particular database?
thank you very much
What is the easy way to get the below data and Time Format.
11/14/2007 3:51:49 PM
Thanks
Venu
In SQL query I have to find records which occour between two dates. I created Select query with two parameters @date1 and @date2 in clasue WHERE. But problem is with date format of my parameters. This format is to long. I dont wont to use time part of these parameters only date part is needed. When I put two identical dates my query doesn't find any data because both dates are eg. 2007-05-22 00:00:00. But I need data for all this day. How to correct this problem? Regards Pawel.
View 2 Replies View RelatedHi, I have a SQL Express 2005 db, with the following format for the datetime vales: dd/mm/yyyy, and if the day or the month has only one digit, it'll be completed with a zero(ie 09/03/2007). The problem is that I sent a backup(.bak file) of it to other people, but they see the date as mm/dd/yyyy and the dates are not completed with zeroes(ie 5/5/2007). How can we both have the same format? Any ideas? Thanks a lot.
View 2 Replies View RelatedI'm still having a problem inserting date fields into sql server.
I don't understand how it accepts datetime.
I have all of my date columns defined with datetime format and all of the dates are coming out as the default of: 01/01/1900.
I tried to insert the data as string and sql server doesn't understand that format.
Here's some of the code:
We're going from flat VSAM files to an sql server database.
This is one huge sql insert statement with about 75 fields being loaded into a table so I'll only post one the date fields.
Here's where I call the String functions from:
First, I have to uncomp the field from binary to String:
ls_sdate = Right$(CompToStr(bufMast.Name_Chg_Date), 8)
And then I send this string to my Convert_Date function:
lsDet1 = Trim$(lsDet1) & Convert_Date(ls_sdate) & ","
(lsDet1 is a concatenated String of the SQL Values to be inserted)
And Here are the two functions:
The date field is coming in like: 1991112 where if the first character is a 1, the year is 1900 and if the first character is a 0, the year is 2000.
I get correct fields in my message box like 1996/12/31 but then I don't know what sql server does to it in datetime format.
When I check the database table it looks like: 01/12/1900
Maybe there is something wrong with my Convert_Date function;
__________________________________________________ ____________
Public Function CompToStr(aCompdata() As Byte) As String
'This is one way in which you can unpack a comp field. As I mentioned,
'you might be better off designing a flexible class to do the
'conversions. At minimum, this function should be expanded to
'accept a data picture as a param (decimal placement and so on).
Dim lsRtnStr As String
Dim lsHoldStr As String
Dim llCount As Long
For llCount = 1 To (UBound(aCompdata) + 1) Step 1 'loop thru the passed array.
lsHoldStr = Hex(aCompdata(llCount - 1)) 'Convert the byte to a Hex string.
If Len(Trim$(lsHoldStr)) = 1 Then 'if the highorder nibble was 0
lsHoldStr = "0" & Trim$(lsHoldStr) 'pad it with a leading zero.
End If
lsRtnStr = lsRtnStr & lsHoldStr 'Concat it to the return string.
lsHoldStr = "" 'clear the var for the next pass.
Next
lsRtnStr = Replace$(lsRtnStr, "C", " ") 'Positive sign replacement.
lsRtnStr = Replace$(lsRtnStr, "D", "-") 'Negative sign replacement.
lsRtnStr = Replace$(lsRtnStr, "F", " ") 'Unsigned - implicit positive.
lsRtnStr = Trim$(lsRtnStr)
llCount = 0
llCount = InStr(1, lsRtnStr, "-")
If llCount > 0 Then
lsRtnStr = Right$(lsRtnStr, 1) & Left$(lsRtnStr, (Len(lsRtnStr) - 1))
End If
CompToStr = lsRtnStr 'Return the hex string.
End Function
__________________________________________________ ___________________
Public Function Convert_Date(ByRef ls_sdate As String) As String
'incoming date
Dim ls_scent
, ls_smonth, ls_sday, ls_syear As String
ls_scent = Left(ls_sdate, 1)
ls_syear = Mid(ls_sdate, 2, 2)
ls_smonth = Mid(ls_sdate, 4, 2)
ls_sday = Right(ls_sdate, 2)
If (ls_sday = "00") Then
ls_sdate = "0000"
ElseIf (ls_scent = 0) Then
'ls_sdate = ls_smonth & "/" & ls_sday & "/" & "19" & ls_syear
ls_sdate = "19" & ls_syear & "/" & ls_smonth & "/" & ls_sday
ElseIf (ls_scent = 1) Then
'ls_sdate = ls_smonth & "/" & ls_sday & "/" & "20" & ls_syear
ls_sdate = "20" & ls_syear & "/" & ls_smonth & "/" & ls_sday
End If
Convert_Date = ls_sdate
End Function
__________________________________________________ ____________
Hi,
I have a table with datetime field in SQL server 7.0
I have inserted values into datetime field
for eg: '10/10/99'
'10/10/99 10:30 AM'
'11/10/99 11:50 PM'
When I view this table from enterprise manager, values are shown exactly
in the same format I have entered.
But, When I run the query from Query Analyzer, It always shows values in the following format '1999-10-10 00:00:00.000'
Is there a way that I can see the values in the same format I have entered.
Help !!!
Thanks
Hi,
I have a datetime field that store data in format like this following format:
2003-05-25 13:01:39.400
How can I change the format of the of the datetime to 13:01:39.400 only.
Thanks
Hello, everyone:
My table has a column that is DATETIME data type. The original format likes "1/21/2004". I want to check data lengh. However when I read the data by SELECT, SQL Server change the format to "Jan.-21-2004" automatically. Does any one has an idea to keep original datetime format? Thanks.
ZYT