Datetime to Time Only

Is it possible in SRS 2K to use a String.Format to change a field of
MM/DD/YYYY HH:MM:SS to display as HH:MM AM/PM only?
ThanksIt is possible. Just go to the properties of the field/ Textbox where you
want to be in time format and go to Format tab. and select format code and
click on "..." you can see time format just select it or it is basically "t"
format.
Amarnath
"lucotc" wrote:
> Is it possible in SRS 2K to use a String.Format to change a field of
> MM/DD/YYYY HH:MM:SS to display as HH:MM AM/PM only?
>
> Thanks

DATETIME To Format mm/dd/yyyy hh:mm am/pm

I have a column in a database set as a DATETIME datatype, when I select it, I want to return it as:

mm/dd/yyyy hh:mm am or pm.

How in the world can I do this? I looked at the function CONVERT() and it doesnt seem to have this format as a valid type. This is causing me to lose my hair, in MySQL it is just so much easier. .

At any rate, currently when I select the value without any convert() it returns as:

June 1 2007 12:23AM

Which is close, but I want it as:

06/01/2007 12:23AM

Thanks!

Hi,

Normally you would be formatting your date on the UI or Report side, which means you must let your program display it correctly or let your reporting engine format your date as you want to.

But if you really want to change the format of your date, you can do this by changing the type to string by using the CONVERT function. The closest that I can come up with is this format:

mm/dd/yyyy

checki it here (code 101):

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

the syntax would be like this in SQL

SELECT CONVERT('your date', nvarchar(MAX), 101)

Maybe you can mix it up with code 108 so that you can concatenate the time with it.

cheers,

Paul June A. Domag

|||

The reason I am formatting it in SQL is this is inside a trigger written in pure SQL which emails from the DB.

Yeah CONVERT() using type 101 was the closest I could get as well, but it only displays date, no time. I need both date and time in the format I specified above.

This is absolutley stupid that they did it this way, they should take a lession from MySQL which allows you to format a DATETIME in any fashion via strings such as %m/%d/%Y etc, etc.

Anybody have other ideas?

|||

Hi,

Why not just combine the codes 101 and 108 and maybe manually parse it using substring?

You can create a scalar function to make it reusable.

Code Snippet

DECLARE @.dt VARCHAR(MAX)

SELECT @.dt = CONVERT(nvarchar(MAX), GETDATE(), 101) + ' ' + CONVERT(nvarchar(MAX), GETDATE(), 108)

-- After this just use Substring to satisfy your formatting

note: varchar(max) is only available in SQL2005. specify the lenght if your using SQL2000

cheers,

Paul June A. Domag

|||

As Paul indicated, formating is normally left to the client application. I suspect the developer you are working with either does not know how to properly format for display in his/her application, or is too lazy and is passing the responsibility off to the database.

This expression should provide the date in the form you desire. Replace the [ @.MyDate ] with your column or date value. You can easily create your own function that will do this for you so that you can re-use this expression.

DECLARE @.MyDate datetime

SET @.MyDate = '2007/07/21 11:35:45.255PM'

SELECT MyDate =
convert( varchar(10), @.MyDate, 101) +
stuff( right( convert( varchar(26), @.MyDate, 109 ), 15 ), 7, 7, ' ' )

MyDate
-
07/21/2007 11:35 PM

|||

Arnie Rowland ,

You are the man, that worked like a charm. I guess my problem with the convert function is that there is no predefined format of:

mm/dd/yyyy hh:mm am/pm

I would assume this is very very popular, so I am confused as to why it is not implemented. As far as this benig done on the front end, this has to be done at the DB level, since we send emails out via the database.

|||

I have to say that I wouldn't want to send an email from a trigger that required any kind of special formatting. Perhaps an alert to a sysadmin, but if I was going to send correspondence like that, I would put my information in a queue of some sort and have a tool to send the email.

the CONVERT thing is a mess because it doesn't give you enough formats, unlike a proper data presentation layer would. I have (in the past) used datePart to build up a date formatter of my own, or you could probably do it with the CLR quite nicely. But SQL Server should be used to manage and manipulate data, not format it (as a broad rule of course. We all do it from time to time to appease a user/manager/programmer etc, so don't think I am saying it is horrible, it just isn't as ideal as using a programming tool made to do such things.)

datetime to dd/MM/yyyy for inserting into DB

hi there, i have a calendar that i put into a string ilke this

string str = Calendar1.SelectedDate.ToShortDateString();

the result is dd/mm/yyyy date which is great, but for inserting into my DB (MSSQL) it needs to be a datetime field, however when i convert it

Datetime dtDate = Convert.ToDateTime(str);

it takes my date and adds 00:00:00 onto the end and this is not what i want! i just want the dd/mm/yyy how do i do this, it has to be simple but i have been searching for hours and cant find anything, i am using ASP.NET 2 and C#

Thanks

The datetime object MUST contain the time. You don't have any problem, if you display only the date, you are OK.

|||

ah rite ok, but when i try to get the date back from the db where its stored as e.g 15/06/2007 the datetime string passes it as 15/06/2007 00:00:00 and so it wont reconise it, is there any other way to pass it so that it can compare?

|||

Hi,

Actually, when recieve the datetime as string from the database, you may convert it to DateTime type and use ToShortDataString method, in this way,you can get the datetime string without time part. See the sample below:

string dt_s = ds.Tables[0].Rows[0][0].ToString();// Get the datetime from db. DateTime dt = Convert.ToDateTime(dt_s);// Convert it to DateTime type.this.Label1.Text = dt.ToShortDateString();// Get the short date

Thanks.

DateTime problem

I need to UPDATE DateTime in database. But input paramter (@.DatumPozadovany) is not in Default format 'mon dd yyyy hh:miAM', but in Europian one 'dd mon yyyy HH:mi'.

This code don't work, becuse it converts standart input into unstandart output. I need the oposite of it.

UPDATE DoslaObjednavka SET DatumPozadovany = CONVERT(datetime, @.DatumPozadovany, 13), Oznaceni = @.Oznaceni WHERE (Id = @.Id)

Please, help.

SELECT CONVERT(DATETIME, GETDATE(),109)

--returns 2007-02-12 21:09:56.970

SELECT CONVERT(nvarchar(26), GETDATE(),109)

--returns Feb 12 2007 9:09:56:970PM

From the SQL Server 2005 Books Online topic
CAST and CONVERT (Transact-SQL)
"In the following table, the two columns on the left represent the style values for converting datetime or smalldatetime data to character data. Add 100 to a style value to obtain a four-place year that includes the century (yyyy).

|||Interestingly, you should try without the convert. If you let SQL do the conversion implicitly, it may well recognise the format you're using anyway. If you specify a format, it will have to match it exactly.

Rob|||Thaks everyone for reply. Error was at very different place.
In ASP.NET i didn't specify parametr datatype. It expected some strange datetime format and didn't work. Now it works fine.

DateTime Parameter need yyyy-mm-dd format

I have a parm defined as DateTime in RS2005 (so you see the calendar control
for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
display yyyy-mm-dd as it was when we had date defined as string rather than
DateTime. I went into regional settings on my pc and changed the date
setting in my regional options to display date as yyyy-MM-dd and it still
didnt seem to work. Can someone direct me? ThanksHi.
You have a language setting in the reports properties.
The default setting for language is "English (United States)". You can
change that either to what the user have choosen in IE by chosing "Default"
or what you want it to be. If you need yyyy-MM-dd you just change to your
country's setting.
You can override this setting for certain textboxes if you like. Textboxes
have their own language setting.
Kind regards
Maran
*********************
"MJT" wrote:
> I have a parm defined as DateTime in RS2005 (so you see the calendar control
> for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
> display yyyy-mm-dd as it was when we had date defined as string rather than
> DateTime. I went into regional settings on my pc and changed the date
> setting in my regional options to display date as yyyy-MM-dd and it still
> didnt seem to work. Can someone direct me? Thanks|||Thank you Maran,
I had changed my regional settings in control panel prior to posting my
message, but I didnt realize that they wouldnt take effect until I closed out
of VS and re-entered. Which brings another question to mind. If we want the
date displayed as yyyy-MM-dd format can that be accomplished solely by
setting to "English(United States)"? Because when I noticed the problem
initially, I had my windows settings for date format as MM-dd-yyyy and my
language was English(United States) and that seemed to be why I saw the date
format as MM-dd-yyyy when I changed my parameter to DateTime format. I am
wondering if the users have their date format in windows set other than
MM-dd-yyyy if just making the language English(United States) will matter at
all now. Since that was the way my settings were originally. Is there a way
to enforce a date time format? I realize that each textbox has a format
parameter, but this textbox that referenced the date parameter had other code
in it which was being translated into multiple languages in a code block - so
I cant use the format property on the textbox itself - I would have to put it
in the code block somehow. Thanks again for your reply.
"Maran" wrote:
> Hi.
> You have a language setting in the reports properties.
> The default setting for language is "English (United States)". You can
> change that either to what the user have choosen in IE by chosing "Default"
> or what you want it to be. If you need yyyy-MM-dd you just change to your
> country's setting.
> You can override this setting for certain textboxes if you like. Textboxes
> have their own language setting.
> Kind regards
> Maran
> *********************
> "MJT" wrote:
> > I have a parm defined as DateTime in RS2005 (so you see the calendar control
> > for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
> > display yyyy-mm-dd as it was when we had date defined as string rather than
> > DateTime. I went into regional settings on my pc and changed the date
> > setting in my regional options to display date as yyyy-MM-dd and it still
> > didnt seem to work. Can someone direct me? Thanks|||I handled this by adding the format to my custom code. Thanks.
"MJT" wrote:
> Thank you Maran,
> I had changed my regional settings in control panel prior to posting my
> message, but I didnt realize that they wouldnt take effect until I closed out
> of VS and re-entered. Which brings another question to mind. If we want the
> date displayed as yyyy-MM-dd format can that be accomplished solely by
> setting to "English(United States)"? Because when I noticed the problem
> initially, I had my windows settings for date format as MM-dd-yyyy and my
> language was English(United States) and that seemed to be why I saw the date
> format as MM-dd-yyyy when I changed my parameter to DateTime format. I am
> wondering if the users have their date format in windows set other than
> MM-dd-yyyy if just making the language English(United States) will matter at
> all now. Since that was the way my settings were originally. Is there a way
> to enforce a date time format? I realize that each textbox has a format
> parameter, but this textbox that referenced the date parameter had other code
> in it which was being translated into multiple languages in a code block - so
> I cant use the format property on the textbox itself - I would have to put it
> in the code block somehow. Thanks again for your reply.
> "Maran" wrote:
> > Hi.
> >
> > You have a language setting in the reports properties.
> >
> > The default setting for language is "English (United States)". You can
> > change that either to what the user have choosen in IE by chosing "Default"
> > or what you want it to be. If you need yyyy-MM-dd you just change to your
> > country's setting.
> >
> > You can override this setting for certain textboxes if you like. Textboxes
> > have their own language setting.
> >
> > Kind regards
> > Maran
> >
> > *********************
> >
> > "MJT" wrote:
> >
> > > I have a parm defined as DateTime in RS2005 (so you see the calendar control
> > > for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
> > > display yyyy-mm-dd as it was when we had date defined as string rather than
> > > DateTime. I went into regional settings on my pc and changed the date
> > > setting in my regional options to display date as yyyy-MM-dd and it still
> > > didnt seem to work. Can someone direct me? Thanks

DateTime parameter format issue?

Hi All:

I create a report with a DateTime parameter, i would love to use DD/MM/YYYY this format, but it doesn't work . it works when i change to usa datetime format ,YYYY-MM-DD,

any helps are appreciated.

thanks

Nick

hi Nick

this is an example of what I use

this is put in as an expression - Layout view on a table

=Format(Fields!Date3.Value, "dd/MMM/yyyy")

hope this helps

|||

Hi, I had the same issue, this is a documented fix in SP1 for SQL 2005, using formatdatetime function you can fix this,

Andy

|||

It is really help.

i used "DD/mm/yyyy", it works

Thanks you very much jewelfire.

|||

Hi,

I have a report which has a datetime parameter,

when i choose the datetime from calendar control , then click view report button. if i choose 13/12/2006, it would give a error, because the report think it's 13 is month , how can i fix this issue. also i want keep dd/MM/yyyy format?

Thanks

Nick

DateTime parameter format issue?

Hi All:

I create a report with a DateTime parameter, i would love to use DD/MM/YYYY this format, but it doesn't work . it works when i change to usa datetime format ,YYYY-MM-DD,

any helps are appreciated.

thanks

Nick

hi Nick

this is an example of what I use

this is put in as an expression - Layout view on a table

=Format(Fields!Date3.Value, "dd/MMM/yyyy")

hope this helps

|||

Hi, I had the same issue, this is a documented fix in SP1 for SQL 2005, using formatdatetime function you can fix this,

Andy

|||

It is really help.

i used "DD/mm/yyyy", it works

Thanks you very much jewelfire.

|||

Hi,

I have a report which has a datetime parameter,

when i choose the datetime from calendar control , then click view report button. if i choose 13/12/2006, it would give a error, because the report think it's 13 is month , how can i fix this issue. also i want keep dd/MM/yyyy format?

Thanks

Nick

Datetime issue

In my DB, the date is in the following format : mm/dd/yyyy hh:mm:ss AM/PM
But wheni read the DB from the SqlDataReader, it returns the date in a different format : dd/mm/yyyy hh:mm:ss AM/PM
Why doesn't it return the date like it is in the DB?
For example, when i open the DB with Entreprise Manager : 5/25/2005 8:26:54 AM
But when i run the Debugger, it reads 25/05/2005 8:26:54 AM
Please help,
Thanks,
I think it may be your Windows regional setting and check you SQL Server configuration properties. Hope this helps.

DateTime from txt

I need to convert a txt field to SQL datetime that looks like the following:
199902250215
So it only has the yyyy/mm/dd hh:mm
SQL does not seem to like that it doesn't have the seconds and miliseconds.
How can I do this?
Thank you!What does it really look like? You show the example without slashes or
spaces yet the format example does have them. At the least you will need a
space in between the days and hours.
Andrew J. Kelly SQL MVP
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:84E1E5E5-3AA0-4264-BE34-64F6FF3CD1B5@.microsoft.com...
>I need to convert a txt field to SQL datetime that looks like the
>following:
> 199902250215
> So it only has the yyyy/mm/dd hh:mm
> SQL does not seem to like that it doesn't have the seconds and
> miliseconds.
> How can I do this?
> Thank you!
>|||Sorry, the data looks exactly like I typed it in (coming from a text field),
not like the one I typed with slashes. Here is what I do to the field when I
import it, but it does not bring the time in, it only updates the year month
and date part of the field.
UPDATE WHJOINTDATA.DBO.FACT_CLAIM
SET WHJOINTDATA.DBO.FACT_CLAIM.policy_date_time =
WHWANGDATA.DBO.WANG_LOSS.pol_date
FROM WHWANGDATA.DBO.WANG_LOSS
WHERE ISDATE(WHWANGDATA.DBO.WANG_LOSS.pol_date) <> 0
AND WHJOINTDATA.DBO.FACT_CLAIM.POLICY_NUMBER =
WHWANGDATA.DBO.WANG_LOSS.LP_NUMBER
"Andrew J. Kelly" wrote:

> What does it really look like? You show the example without slashes or
> spaces yet the format example does have them. At the least you will need
a
> space in between the days and hours.
> --
> Andrew J. Kelly SQL MVP
>
> "Patrice" <Patrice@.discussions.microsoft.com> wrote in message
> news:84E1E5E5-3AA0-4264-BE34-64F6FF3CD1B5@.microsoft.com...
>
>|||What you need to do is add a space and a : to make it work with a CONVERT
such as this:
select CONVERT(DATETIME,LEFT(@.pol_date,8) + ' ' + SUBSTRING(@.pol_date,9,2) +
':' + SUBSTRING(@.pol_date,11,2))
Andrew J. Kelly SQL MVP
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:30741AD2-4355-42DB-8623-81A1728BFFA9@.microsoft.com...
> Sorry, the data looks exactly like I typed it in (coming from a text
> field),
> not like the one I typed with slashes. Here is what I do to the field when
> I
> import it, but it does not bring the time in, it only updates the year
> month
> and date part of the field.
> UPDATE WHJOINTDATA.DBO.FACT_CLAIM
> SET WHJOINTDATA.DBO.FACT_CLAIM.policy_date_time =
> WHWANGDATA.DBO.WANG_LOSS.pol_date
> FROM WHWANGDATA.DBO.WANG_LOSS
> WHERE ISDATE(WHWANGDATA.DBO.WANG_LOSS.pol_date) <> 0
> AND WHJOINTDATA.DBO.FACT_CLAIM.POLICY_NUMBER =
> WHWANGDATA.DBO.WANG_LOSS.LP_NUMBER
> "Andrew J. Kelly" wrote:
>

DateTime Formatting

Have a cell in a table that is being populated by a field in database that
contains the date and time in this format mm/dd/yyyy hh:mm AM. I need it to
be the otherway around ie. dd/mm/yyyy hh:mm AM/PM. But when i try and use
=Format(DateTime.Value, "dd/MM/yyyy hh:mm") is get an error saying that the
hh is not declared.
Any help on this would be greatly appreciated.Forget the format function. Right click the textbox and go to properties,
under format section click the custom radio button now you can define what
you want: "dd/MM/yyyy hh:mm tt" or whatever.
--
Message posted via http://www.sqlmonster.com

Datetime format regardless regional settings

Hi,

in tsql there is a common format for datetime (regardless regional settings)?

I use : 'mm/dd/yyyy' to access cols in database,

but someone says that the right one is: yyyy-mm-dd.

Any suggestion about that?

Thank a lot

I believe you want the ISO format -- 112; that is yymmdd. Can somebody double check this?

Dave

|||

yes dave.. 112 is the ansi unseperated date format.....

so best option is to get all ur date in this format: select convert(varchar, getdate(), 112)

|||

Hi,

I tried thid on nortwind DB (date are: dd/mm/yyyy)

select * from orders where orderdate < '19960704'

this does work, no data are retrieved,

but works for:

select * from orders where orderdate < '07/04/1996'

select * from orders where orderdate < '1996-07-04'

both select one row (if nortwind is the original one)

?

any suggestion?

All extract the same regardless the regional settings?

|||

Fast:

I tried all three queries against my copy of the northwind database. The lowest date that I have in the orders table is the date '07/04/1996'. For this reason, I do not get any rows returned with any of the select statements. However, when I change the "less than" operator to a "less than or equal to" operator I get the expected row. I am not getting the same results as you. I am afraid I can't help on this one. Sorry fo adding to confusion.

Dave

|||

hmm..works fine with me...the lowest date in northwind is..1996-07-04 00:00:00.000..

i get 0 rows for all the queries u mentiones...and 1 each when i use <= ..

neways...wat do u want to achieve exactally....see 112 is the ansi date format, which shud be used for date conversion and comparisons as its the standard....select convert(varchar(10), getdate(), 112) ...

yyyy-mm-dd +time is how sql server present it as..

|||

Sorry for having written too fast,

all three select retrieve 1 record if date is minor than 5 july 1996,

I have tried all queries with several regional settings and everything work.

If anyone has other suggestion, is welcome

Besides in internet there are this interestings articles:

http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-delimit/format-dates-for-database-entry.html

and

http://www.karaszi.com/SQLServer/info_datetime.asp

Thank

DateTime Format Conversion Issue

Hello everyone,

I came accross this problem where I can't change my stored date to:

m/d/yyyy hh:mi:ss AM

Seems to me like there is no code related to that particular conversion.

I'm just assuming that because I've tried all the codes supplied with the convert, and the closest that I got was

selectCONVERT(nvarchar(max),getdate(),22)

02/27/07 12:24:40 PM

m/d/yy hh:mi:ss AM

So can any one give me a quick solution for this? I'd appreciate it

select

RIGHT(CAST(100+DATEPART(MM,createdDate)ASCHAR(3)),2)+'/'

+RIGHT(CAST(100+DATEPART(DD,createdDate)ASCHAR(3)),2)+'/'

+CAST(DATEPART(YYYY,createdDate)ASCHAR(4))+' '

+CASEWHENDATEPART(HH,createdDate)< 13

THENRIGHT(CAST(100+DATEPART(HH,createdDate)ASCHAR(3)),2)

ELSECAST(DATEPART(HH,createdDate)-12ASCHAR(2))

END+':'

+DATENAME(ss, createdDate)+':'

+RIGHT(CAST(100+DATEPART(MI,createdDate)ASCHAR(3)),2)

+CASEWHENDATEPART(HH,createdDate)< 13

THEN' AM'

ELSE' PM'

END

from files

|||

How about

selectCONVERT(nvarchar(max),getdate(),101)+substring(CONVERT(nvarchar(max),getdate(),22), 9, 50)

?

Dan

DateTime Format Codes

Hi everyone (again). I'm having trouble with some sate formats with the convert() function (I'm not finding the correct format code for "YYYY" format). Is it 100?
Sorry for the silly question
Thanks and best regards
Rafael Mauricio Nami
There is no direct style to get just the year format. You can do one of the following:

select cast(year(current_timestamp) as char(4))
, cast(datepart(year, current_timestamp) as char(4))
, convert(char(4), current_timestamp, 112)

DATETIME FORMAT

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

Hi I think this will be useful

http://sqlserver2000.databases.aspfaq.com/can-i-make-sql-server-format-dates-and-times-for-me.html

Datetime datatype conversion to int hhmmyy format

Is there a way of converting a datetime data type in the form [DD/MM/YYYY HH:MM:SS] to an integer containing just the time in the form [HHMMSS].SELECT REPLACE((CONVERT(VARCHAR,GETDATE(),24)),':','')

This converts it to a varchar. You actually don't want to convert it to integer as it will drop off the leading zeros.

Datetime conversion under diferent versions of SQL

You are probably passing dates in some regional format (e.g. dd/mm/yyyy) and
this is okay on one server (which may have British language settings) but
not on another (which may have US English language, or mdy dateformat). To
avoid these problems, always pass dates as 'YYYYMMDD'...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"kTodos" <kanduru.x@.iol.pt> wrote in message
news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I'm using the same script to insert/update records on diferent versions of
> SQL but i'm getting this error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]A converso de um tipo de
> dados char em um tipo de dados datetime resultou em um valor datetime fora
> do intervalo.
> (translation: error converting one string into datetime value out of
> range)
> The SQL versions that I am probing is 8.00.194 (RTM) that is installed
> with Microsoft SQL Personal Engine CD and ther other version is 8.00.2039
> (SP4) that i've downloaded and installed.
> Can anywone help me?
> Regards,
> kTodos
>
... and for some extra reading: http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OtJcSa8rHHA.1200@.TK2MSFTNGP04.phx.gbl...
> You are probably passing dates in some regional format (e.g. dd/mm/yyyy) and this is okay on one
> server (which may have British language settings) but not on another (which may have US English
> language, or mdy dateformat). To avoid these problems, always pass dates as 'YYYYMMDD'...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "kTodos" <kanduru.x@.iol.pt> wrote in message news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
>

Datetime Conversion Question

If I have a variables that are declared as

@.Start_Year int

@.Start_Month int

How do I convert those variables to datetime format of

MM/1/YYYY

If what you want is datetime:

select convert(datetime, convert(varchar(2), @.start_month)

+ '/1/' + convert(char(4), @.start_year))

If what you want is a formated string in which the month contains a potential leading zero:

select right('0' + convert(varchar(2), @.start_month), 2) +

'/1/' + convert(char(4), @.start_year)

Datetime conversion from csv file

I have a DTS-package running which imports data from a .csv file to a sql2000 database.
In the file there are some datefields in dd/mm/yyyy format and i want to keep it that way. But after the import the dateformat is yyyy/mm/dd.
Does anybody know how i can prevent this from happening?

Thanks in advanceIn MS-SQL a datetime field is typically displayed as 'yyyy/mm..'. Internally it is stored as an 8-byte value counting from 1973. If you'd like to change the way the datetime is shown, I'd suggest to use 'convert'.

Datetime and conversion to smalldatetime.

I am placing DateTime into SQL using an ASP.NET form. The date should be formatted dd/mm/yyyy hh/mm/ss.

I am getting the error below. Is there any way to convert the format of the DateTime function from the ASP.NET end?

Thanks

mes

"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value"

DateTimes aren't "formatted". It sounds like you are giving it a string, that you think looks like a date. Give the database a real datetime (Or specify the sqlparameter as being of datetime type), and your problem should go away. If that string format isn't valid for your culture, well... You'll have to fix that separately, or manually convert your "DateTime in a string" to a format that your SQL Server wants.

DateTime ?

This is my table structure

Date(m/dd/yyyy)

9/09/2006

I want to select month and year in the below format .

Sep 2006 .

How to do that ?

Try this..

SELECT CONVERT(CHAR(6),GETDATE(),109)

|||

Sorry Raghu,

Try this..

SELECT LEFT(CONVERT(CHAR(11),GETDATE(),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),GETDATE(),109),4)

|||

Or:

SELECT LEFT(DATENAME(month,'9/09/2006'),3) + ' ' + CONVERT(CHAR(4),Year('9/09/2006')) as DateYouwant