Date Parsing using T-SQL

I can do this using MS Access 2000 database code.
String called
DKEY: "199306 30"
Using MS Access to convert that to a date the following code works:
DateValue(Mid(Replace([dkey]," ",""),5,2) & "/" & Mid(Replace([dkey],"
",""),7,3) & "/" & Mid(Replace([dkey]," ",""),1,4))
Returns the date value: 6/30/1993
How is this done using T-SQL? Any help greatly appreciated!!!
RBollingerIf your datestring is always formatted in that fashion (ie,
"YYYYMM[spaces]dd") , then the easiest thing to do is to remove the
spaces and convert it to a date.
SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
HTH,
Stu
robboll wrote:
> I can do this using MS Access 2000 database code.
> String called
> DKEY: "199306 30"
> Using MS Access to convert that to a date the following code works:
> DateValue(Mid(Replace([dkey]," ",""),5,2) & "/" & Mid(Replace([dkey],"
> ",""),7,3) & "/" & Mid(Replace([dkey]," ",""),1,4))
> Returns the date value: 6/30/1993
> How is this done using T-SQL? Any help greatly appreciated!!!
> RBollinger|||Convert(datetime, Replace([dkey], ' ',''), 112)
Tom
"robboll" <robboll@.hotmail.com> wrote in message
news:1149630420.422200.35050@.u72g2000cwu.googlegroups.com...
>I can do this using MS Access 2000 database code.
> String called
> DKEY: "199306 30"
> Using MS Access to convert that to a date the following code works:
> DateValue(Mid(Replace([dkey]," ",""),5,2) & "/" & Mid(Replace([dkey],"
> ",""),7,3) & "/" & Mid(Replace([dkey]," ",""),1,4))
> Returns the date value: 6/30/1993
> How is this done using T-SQL? Any help greatly appreciated!!!
> RBollinger
>|||thanks!
Tom Cooper wrote:
> Convert(datetime, Replace([dkey], ' ',''), 112)
> Tom
> "robboll" <robboll@.hotmail.com> wrote in message
> news:1149630420.422200.35050@.u72g2000cwu.googlegroups.com...|||I get an error when you trying this. In Access you have to accout for
a - or / or . delimiter between dates. Does the same apply to T-SQL?
SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
Stu wrote:
> If your datestring is always formatted in that fashion (ie,
> "YYYYMM[spaces]dd") , then the easiest thing to do is to remove the
> spaces and convert it to a date.
> SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
> HTH,
> Stu
>
> robboll wrote:|||I get an error when trying this. In Access you have to accout for a -
or / or . delimiter between dates. Does the same apply to T-SQL?
Tom Cooper wrote:
> Convert(datetime, Replace([dkey], ' ',''), 112)
> Tom
> "robboll" <robboll@.hotmail.com> wrote in message
> news:1149630420.422200.35050@.u72g2000cwu.googlegroups.com...|||SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5, 2) + '/' +
SUBSTRING(dkey, 1, 4)
provides a readable format, but there are no leading zeros in the day
like there are in the month, and it's still a text string.
convert(smalldatetime, SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5,
2) + '/' + SUBSTRING(dkey, 1, 4)) doesn't seem to work
robboll wrote:
> I get an error when trying this. In Access you have to accout for a -
> or / or . delimiter between dates. Does the same apply to T-SQL?
> Tom Cooper wrote:|||Okay -- This works. Y'all got me on the right track. Thank you!
CONVERT (smalldatetime, SUBSTRING(dkey, 5, 2) + '/' +
LTRIM(SUBSTRING(dkey, 9, 3)) + '/' + SUBSTRING(dkey, 1, 4))
robboll wrote:
> SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5, 2) + '/' +
> SUBSTRING(dkey, 1, 4)
> provides a readable format, but there are no leading zeros in the day
> like there are in the month, and it's still a text string.
> convert(smalldatetime, SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5,
> 2) + '/' + SUBSTRING(dkey, 1, 4)) doesn't seem to work
>
> robboll wrote:|||What error did you get? In SQL, a format of 20060601 is preferred;
it's unambiguous, and the pattern you provided should have matched
that. The only thing I can think of is that there must be some other
delimiters besides spaces.
I see you found a solution, but was just curious.
robboll wrote:
> I get an error when you trying this. In Access you have to accout for
> a - or / or . delimiter between dates. Does the same apply to T-SQL?
> SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
> Stu wrote:|||For some reason I think the problem had to do with an inconsistent
string value where the month had leading zeros and the days didn't. To
get around this I used an acceptable date delimiter "/". But you're
absolutely correct it will work with the string date in your example.
Unfortunately mine was 2006061.
Stu wrote:
> What error did you get? In SQL, a format of 20060601 is preferred;
> it's unambiguous, and the pattern you provided should have matched
> that. The only thing I can think of is that there must be some other
> delimiters besides spaces.
> I see you found a solution, but was just curious.
> robboll wrote:

Date Format

Hi All,
I am new to T-SQL and SQL 2005.
I would like to use this command that I used to with Access.
SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
"#,###.00") AS orderQuantity
FROM myTable
How can I do those thing in T-SQL?
Thank you in Advance
Regards,
Gun
See my reply in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gunawan" <jgun98.milis@.gmail.com> wrote in message news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>

Date Format

Hi All,
I am new to T-SQL and SQL 2005.
I would like to use this command that I used to with Access.
SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
"#,###.00") AS orderQuantity
FROM myTable
How can I do those thing in T-SQL?
Thank you in Advance
Regards,
GunSee my reply in .programming. Please don't multi-post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gunawan" <jgun98.milis@.gmail.com> wrote in message news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>|||Hi
Read more on convert/cast functions in sql bol
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Gunawan" <jgun98.milis@.gmail.com> wrote in message
news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>

Date format

I'm trying to build a file-name variable that begins with a 'date'.

For example, I need to build logic in a T-SQL script that will access a file called 08032006abc123, with '08032006' being a representation of the date 8/3/2006.

Is there a quick/easy way to create this string, based on the date 8/3/2006? (would be nice if there was a date function that would zero-fill)

If you can use the date format YYYYMMDD or YYMMDD you can use CONVERT to get the encoded date ( http://msdn2.microsoft.com/en-us/library/ms187928.aspx ) else you can always do this and then rearrange the out string using the SUBSTR|||

The following syntax will return the date in the manner you are looking for:

DECLARE @.date datetime

SET @.date = GETDATE()

select replace(convert(varchar(10), @.date, 101), '/', '')

Results: 09082006

Date Format

Hi All,
I am new to T-SQL and SQL 2005.
I would like to use this command that I used to with Access.
SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
"#,###.00") AS orderQuantity
FROM myTable
How can I do those thing in T-SQL?
Thank you in Advance
Regards,
GunSee my reply in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gunawan" <jgun98.milis@.gmail.com> wrote in message news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.g
bl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>|||Hi
Read more on convert/cast functions in sql bol
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Gunawan" <jgun98.milis@.gmail.com> wrote in message
news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>

Date and time stamp

Hi im having a few problems saving a file with the data and time i am
using the below t-sql but can only get the date and not the time.
CONVERT(NVARCHAR(16),GETDATE(),112)
Any guidance would very much be appreciated.
thanks in advanceWhat is the exact format you need it in? 112 converts to a CHAR(8)
basically, YYYYMMDD.
For some other possibilities, see http://www.aspfaq.com/2464
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140636158.729737.51920@.f14g2000cwb.googlegroups.com...
> Hi im having a few problems saving a file with the data and time i am
> using the below t-sql but can only get the date and not the time.
> CONVERT(NVARCHAR(16),GETDATE(),112)
> Any guidance would very much be appreciated.
> thanks in advance
>|||convert(varchar(50),getdate(),9)
If you look towards the bottom of my blog, I have compiled a list of
diffrent datetime styles.
HTH
MJKulangara
http://sqladventures.blogspot.com|||There are many and you were on the right track for one way.. :)
CONVERT(NVARCHAR(16),GETDATE(),112) +
REPLACE(CONVERT(NVARCHAR(12),GETDATE(),1
14),':','')
Good luck|||hehe you rated your own post :P|||Unintentional...apparently just clicking on it by accident does it.
Don't touch my hubris :-)|||The format i posted outputs
20060222150720407
year
2006
month
02
date
22
hour
15
minute
07
second
20
millisecond
407
then you can append .txt to the end of that|||MJKulangara wrote:
> Unintentional...apparently just clicking on it by accident does it.
> Don't touch my hubris :-)
Gmail is so <3 gmail|||Jebuskrust,
Many thanks that has gave me the above format but idealy i would like
just 200602221507
year
2006
month
02
date
22
hour
15
minute
07
Can this be done'|||yes change the nvarchar(12) to a nvarchar(8) to show instead of 12
chars to show the first 8 instead. (cheers!)