Hello all,
I was wondering if someone can assist me with the following date validation: i'm looking at somebody's sql and not sure what it means
CONVERT (varchar, dbo.tblSS_Milestone.dtBaselineStart, 101)
what does 101 mean?
Also can someone show me how i can check if there are nulls in the field and if there is, i would like to use another field with a value.
I'm not sure how to do that in sql server. Thank you.
i have two params..
@startdate datetime,@enddate datatime
1.what's the best way to validate them
2.Best way to check whether @startdate is less than @enddate
I am new to SQL SERVER,
Can anybody help me to write a procedure to validate a given string like
09461020(Y2K compliant date), I need to check wether this is a valida date or not??
0946 = should convert to 1946
10 = month
20 is the date.
If it is 20461020.....then year = 2046, month = 10 and day = 20..and it is valid.
procedure should return validate date or not ...return TRUE OR FALSE.
Guys,
I hope I will be able to get this.
I'm looking a cripts/code that will validate date in my form when the user key in date. The date has to be in the following format 00/00/0000 (month/ day/ year)
HELP
Thk
hi,
this is my table values:
select * from empmaster
emp.no fromdate todate
----------------------------------
E3/13/20074/7/2007
E4/13/20076/13/2007
i am going to pass some date for example 5/20/2007.
so i have to compare this 5/20/2007 which lies in between 3/13/20074/7/2007 or 4/13/20076/13/2007.if means i have to print not allowed other wise allowed. and the date should not be less(if 2/10/2007). like thi i have many from date and todate in my table. if i give 7/20/2007 means it should print allowed becos it doesnot falls in between any date o the emp.no=e. this should be depent on the emp.no also.
so please help me to do this.please show me query for this.or spc
For the incoming data from a text file i want to check if the dates are valid...how can i do that?????
View 4 Replies View RelatedHi There,
I have a software where the frontend is created in VB and backend is created in SQL server. Our HO sends a file, this file has only one month validity. It has 2 files one is parent and one is child file. while uploading the file using software it creates the following files
Clipboard Clip File, Cursor File, DBE file, EXM, GLB file, MOD File, SQL server subscription file, MTB file, PAT file, PSK file, PTD file, QCM file, QTY file, SEM file, SMO file,STR file, TOP file and a VLP file.This files uploads questions and answers in database. and in SQL Profiler it runs the following queries
select dbname from master ..syslogins where loginname =UPPER ( 'S' )
select convert ( varchar ( 10 ) , getdate ( ) , 105 ) from sysusers where uid =0
select convert ( varchar ( 10 ) , getdate ( ) , 105 ) + ' ' + convert ( varchar ( 8 ) , getdate ( ) , 108 ) from sysusers where uid =0
select convert ( varchar ( 10 ) , getdate ( ) , 120 ) from sysusers where uid =0
All of them returns date. But after this it throws error file expired. I dont understand from where it checks date. and which file contains questions and answers
Can anyone please tell me which file might contain date validation and how do i remove it. I can mail the files for your kind reference.
Thanking you and waiting for some kind replies
mystical
Hi all,
I have a report that have 2 date parameters (start and end date). How would I check that end date parameter move be equal or greater than Start date?
thanks
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
Hi,
Basically the above is a very common requirement, please comment on my solution which I've arrived at by searching through the web; -
In summary I have used 3 SSIS components these are "Flat File Source", "Derived Column" and "SQL Server Destination".
1) File Connections Manager Editor
1.1) Within File Connections Manager Editor; -
Name the data type e.g. "INTERCHANGE_NET_APP_DATE_SRC"
and assign a type to the data type e.g. string[DT_STR]
1.2) Click on the Preview button to ensure the expected text is assigned to the expected data type.
2) Derived Column Transformation Editor
2.1) Assign Derived Column Name, e.g.
INTERCHANGE_NET_APP_DATE
2.2) Select <add as new column> within Derived Column.
2.3) Enter the conversion Expression, e.g. ; -
2.3.1)
(SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,8,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,5,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,1,4))
2.3.2)
Since the above conversion is such a common task I suggest that Service Pack 3 of SQL Server 2005 delivers the following functionality; -
STRINGTODATE ('YYYYMMDD',INTERCHANGE_NET_APP_DATE_SRC)
2.4) Select "database timestamp [DT_DBTIMESTAMP] " as Data Type.
2.5) Within the Mappings tab of the SQL Destination Editor have; -
Input Column as INTERCHANGE_NET_APP_DATE and
Destination Column as INTERCHANGE_NET_APP_DATE.
Please comment on the above, I will then pass on my suggestion to Microsoft.
Thanks in advance,
Kieran.
Hi All
I am trying to copy data from one table to another with a conditional split to validate the date.
In my source table, the date field open_date is stored as string YYYYMMDD (19941231) while in my
destination table it is stored as a datetime field.
My source date has bad data in the field, ie invalid dates e.g ( 20012017, 19701043).
I wanted to use a conditional Split to filter records with bad dates into a file
I was looking for a function like ISDATE (open_date) = True then go to table
else put in file. But have not been able to find one.
Or if they is another way to achieve this, I would be interested.
Thanks much in advance
I have two columns in Informix data base One has Data Type of date and another column of data type string.
Time is stored in string format. I have to Validate wether both are correct, not null, greater than 1753 and concate to get one datetime field to transfer to SQL Server.
Right now I am doing it in script component, as I need to log error if any thing is wrong
Is there any better way to do it,(derived column or any other component) so that I can log the error also.
Thanks
Dharmbir
This is driving me nuts..
I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.
I'm struggling with this for a quite a while and I'm not able to get it working.
I tried the oracle query something like this,
SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE
FROM TBLA
this gives me an output of 20070511 23:06:30:000
the space in MM : SS is intentional here, since without that space it appread as smiley
I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error
The value could not be converted because of a potential loss of data
I'm struck with error for hours now. Any pointers would be helpful.
Thanks
i do have date problem in sql server, i m using DD/MM/YYYY date format, & passing it to insert & update stat...& compairing it with data in table, which is not working properly, how to convert dd/mm/yyyy to mm/dd/yyyy or yyyy-mm-dd
hoping for solution soon, thanx
murli ......
I'm searching on a smalldatetime field in SQL Server so a typical value would be 09/21/2005 11:30:00 AM. I have a search form which offers the user a textbox to search by date and unless they enter the exact date and time, no matching records are found. Of course I want I all records for a given day to be returned. This is how I'm doing it now. Thanks.
Dim dteDate_Requested As String = txtDate_Requested.Text
If dteDate_Requested <> "" Then strSqlText += " Date_Requested='" & dteDate_Requested & "'"End If
HI everyne,
I have a varchar field in one table, which contains data in the form '010706' and I want to convert this to date datatype to 01/07/2006 (Jan 07, 2006). When I just import the data to the other table it gets converted to 7/6/2001, how can I convert it right? Please help.
Hello All,
I need help in converting a date. What i'm looking for is date in format of mm/yyyy.
Thanks in advance.
I need to import a text file into a table by using DTS.
How to convert a text date to smalldate type ?
Thanks.
Hi all
I wonder whether any of you can help me with a bit of code that you may have already had to execute??
I have a SQL database logging activities and a load of information in a mdb file that needs to be imported.
Unfortunately the data in the SQL database is in the format yyyy-mm-dd and the data in the mdb file is in dd/mmmm/yyyy.
When i run a DTS to import the data the new rows are imported as they were YYYY-dd-mm.:mad:
example:
data logging as
2003-08-10
2003-08-11
imported data from last week arrives as
2003-01-08
2003-02-08
2003-03-08
etc
how can i manipulate the data in SQL to reverse the day and month numbers for Aug 1st to Aug 8th??
I have tried changing the mdb data format but that doesnt make a difference. I dont understand DTS enough to know whether it is possible there :confused: and my SQL skills dont rise to the challenge - yet!! :o
TIA
I have a datetime field in a table and I have to insert this datatime data into antoher table. In my insert statement I convert the datetime field into varchar and then insert it into the second table.
The date field in the original table is : 2/2002/13 3:58:12 PM
but in the destination table i get: 2/2002/13 3:58:00 PM
I lose the seconds in the conversion, i think
Whats the best way to preserve to the datetime field during transfer?
thanks
I have one column that is a datetime, and another that is an INT which represents seconds. i cannot figure out how to subtract the seconds from the datetime column. sorry, i'm still kind of new to this TSQL. I get this error:Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
when i try to do this
select dateColumn - IntColumn from Table
so i think there must be a way to make sql know that IntColumn is actually seconds. thanks
Is use this stored procedure.
This is the error mesage: "Syntax error converting datetime from character string"
Please help me !
Alter Procedure "Selectie_Date_Tabel" (@datainceput datetime, @datasfirsit datetime,@Grupa AS nvarchar(20))
As
set nocount on
DECLARE @NEWLINE AS char(1)
SET @NEWLINE = CHAR(10)
DECLARE @keyssql AS varchar(1000)
SET @keyssql = 'SELECT * FROM View2'
+ @NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @Grupa + CHAR(39)
+ @NEWLINE + 'AND ([Day] BETWEEN ' + CONVERT(DATETIME, @datainceput , 120) + ' AND ' + CONVERT(DATETIME, @datasfirsit , 120) +')'
EXEC (@keyssql)
Hi,
I have my dates in DB2 source in two formats -
Format 1 - char(5) - Example - 10305. 1 indicates century,03 indicates year and 05 indicates month. The day is not stored. So this is 2003,May 1
Format 2 - char(7) - Example - 1030525. 1 indicates century,03 indicates year, 05 indicates month,25 indicates day. 2003,May 25
I want to convert the above two formats to SQL Server smalldatetime and I only need the DATEPART. The date needs to be in the format mm/dd/yyyy. The default day would be 01 when the day is not specified.
If the format is 00305 then the 0 indicated 19th century. So this is 1903, May 1.
Any help is appreciated.
Thanks,
Vivek
Hi am trying to convert my date from the date times stap to
this format 250408
I've tried this : select convert(varchar,getdate(),112)
but the result am getting is '20080425'
priya writes "select convert(smalldatetime,'09/06/2006') is working
select convert(smalldatetime,'13/06/2006') it shows an error
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
why it shows an error"
I have a SQL database where the dates appear in the format "733433".
If you convert in the "Select" statement, it's fine. You can use the day, month, year concatenated in an excel expression, converts fine. As an expression in reporting services, I receive an error. An help is appreciated.
I need help with date conversion from character data. In SQL 2000 we used a Date Time Conversion task
I do not see how to do this in SQL 2005 SSIS. I tried a data conversion task to a database timestamp and this is what I got:
[Data Conversion [383]] Error: Data conversion failed while converting column "date_time_stamp" (47) to column "Copy of date_time_stamp" (396). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Here is a sample of the input data I'm trying to convert.
input data example - 2006-03-07-14.42.34
Any ideas? .
Hi,
My source is flat file and my destination is SQL SERVER 2005 using SSIS TOOL.
In my source file i got a date column which is in ISO standards ex: 20050131
I have taken source flat file data type as database date [DT_DBDATE] and in
destination table i declared data type as datetime.
When i start debugging i am getting an error saying that data conversion is not possible.
Can you please help me out how to solve the problem, what data types do i need to take in source and destination and is there any necessity of using Data Conversion Transformation.
If, so please tell me how to do.
With Regards
Satish D
I have a table with about 20,000 records that have a date field, stored as a datetime in the database like '8/28/2006 8:42:14 AM'. The dates range from March 2004 to current. What I would like to do is retrieve the dates in that format (month year) and put them in a dropdown. I have this so far:SELECT DISTINCT DATEPART(month, dte_date) AS Expr1, DATEPART(yyyy, dte_date) AS Expr2 FROM myTable ORDER BY DATEPART(yyyy, dte_date), DATEPART(month, dte_date)And the query returns the information that I want, but I can only bind one field to the dropdown. I was thinking that if I return the results a single expression (concantenate?) then I could bind that to the dropdown. I'm not sure as how to go about this. Also, the month returned is numeric and I would rather have the name of the month returned (like "July" instead of "7"). Thanks in advance to anyone who helps me.
View 4 Replies View RelatedI'm trying to insert a date, I don't want the current time. It would be ok if it was 12:00:00. I have tried using the below bold area, but I get an error that the conversion is not correct.
Any ideas?
INSERT INTO tblpayments (rec_ID,client_ID,tranDate,tranAmount,DateEntered,EnteredBy)
SELECT rec_ID,client_ID,Convert(varchar(5), GETDATE(), 10) AS TranDate,Tranamount,DateEntered,EnteredBy
I'm setting up a website for a new employer and their existing database.
The table I'm using has a Time field in it that captures the date and time of the record.
I'm trying to tie that field into some label controls and can't seem to figure out how to convert the time to just a date. I tried Format(TimeColumn, "MM/DD/YYYY") which does nothing but put my intended formatting as the label. What do I need to do to convert the time to just a date?
Hi:
here i am giving one example with Emp table and fields ssn,empjoindate
I have a table with field empjoindate as varchar(10).I need number of employees joined between 12/23/01 and 01/04/02.I ran this query using select ssn from EMP where convert(varchar(10),empjoindate,101) between '12/23/01' and '01/04/02'.it is not fetching any data.
Please help me
Thanks
Tej